Access query will not list certain date

[mtom5]

I have a query that reports on records according to date. The criterea
line of the date field has [Enter Date].
I have confirmed that there are dates from 8/1/06 through 8/10/06 in the table, but when I enter the date 8/10/06 the query returns no records. But it does return records for all other dates (8/1/06 through 8/9/06 inclusive).
So then I tried to make the date a range:
Between [Enter Start Date] And [Enter End Date].
I entered 8/9/06 and 8/11/06 in these fields and there in the report were the records for 8/10/06. Then I went back to [Enter Date] as criterea and entered 8/10/06 and no records are returned.

I am stumped and hoping someone here has an idea to try.

Thanks

mtom5

 

[FoFa]

You problem is time protion of the date. A date without the time portion default to midnight of that date. If the rest of those dates have a time element on them, past midnight, they do not meet your criteria. Example 08/10/2006 00:00:00 is less than 08/10/2006 13:00:00. So you can either query a day ahead (and hope you have no record exactly at midnight), or you can specify a time parameter 23:59:59 as part of your date criteria, or you can only query the date protion by stripping the time portion from the table, or you can add a day to the entered date in your query so the day ahead thing is transparent (the first one I gave you). Just as some ways to resolve your issue. You can also change any code storing the date to use DATE() instead of NOW() so it is stored without the time in the table.

 

[mtom5]

I am not sure I understand. If I enter 8/9/06 all records are returned with the date of 8/9/06 regardless of the time, but when I enter 8/10/06 no records are found. It is true that the default for this field was NOW() but I checked and none of the records for 8/10/06 have 00:00:00 as the time associated with them.

 

[FoFa]

That would be correct, but when you ENTER 08/10/2006, it defaults to 00:00:00 as the time. Try entering 8/10/2006 23:59:59 instead and see what happens