Query Filter (1 Viewer)

[sara82]

I am having trouble with the query producing the correct Dates.

I have the Log Date Field and for the criteria I have:

Between Nz([Forms]![frmDate]![txtStart],[LogDate]) And Nz([Forms]![frmDate]![txtEnd],[LogDate])

When I run the query it asks for Forms!FrmDate!txtStart I enter 10/1/05
It then asks for Forms!FrmDate!txtEnd I enter 10/31/05

The results produced are wrong. It shows me records fro 10/1/05 to 10/29/05 it is not including 10/31/05;

Also for Start if I enter 10/26/05 and End I enter 10/26/05 It gives me 0 records when there are 3 records for that date.

Attached is the db. I'd really appreciate it if someone can take a look at it.

 

[Ziggy1]

Lose the Between function, use:

>=[Forms]![frmDate]![txtStart] And <=[Forms]![frmDate]![txtEnd]

And select your date from the form and then run the query from the form. If you want a report to run from the criteria make sure it is based on the query (or one with the same criteria).

The query gets the criteria from the form so the form has to be open first.

 

[sara82]

I think I may have solved the problem. I'm still testing it out to make sure.
The Log Date was storing both the Date and Time. LogDate is being populated by (Now) in the form's Before Insert Event.

I had: Me.LogDate = (Now)

I changed it to Me.LogDate = Format(Now, "mmm d yyyy") So that only the date will get stored.

Once I did that it allowed me to filter b/w my start and end dates and it included everything in that range.

 

[sara82]

Quick question, if I entered the Start Date as 11/1/05 and I left the End Date blank shouldn't it return results starting at 11/1/05 to present?

At the moment if I do that (leave the End Date as blank) it returns all records even those before 11/1/05.

 

[Ziggy1]

sorry, I'm not sure on that one, I tried using the LIKE operator with a default value "*" in the text controls but it would not work.

The example I gave does work but not for if you leave the end date blank.