Peculiar date range behavior

[jkfeagle]

I have a form that is based on a query that requires the user to choose a start date and end date for the data to display. The query looks to the text boxes on the form for the criteria for the date field (between .... and .....). For some reason, when you select the same start and end date, it won't display data even if there are records for that date. Selecting a two day range will display it, but not one. Why is that and how do I get around it???

 

[FoFa]

Sounds like date/time issue.
If you supply a date with no time, it defaults to 00:00:00 (basically midnight). If you store the dates with a time, but do not use a time in the query it basically would be like this:
Params:
Start Date = 01/02/05 End Date = 01/02/05
What Access sees:
Give me everything where the dates are between 01/02/2005 00:00:00 and 01/02/2005 00:00:00

So how many dates are between those dates?
If you stored a time on the DB it might be 01/02/2005 08:13:24

So does 01/02/2005 08:13:24 fall between 01/02/2005 00:00:00 and 01/02/2005 00:00:00?

No.
But it does fall between 01/02/2005 00:00:00 and 01/03/2005 00:00:00, so your next day works.

 

[jkfeagle]

That was it. Thanks FOFA!

 

[Jon K]

Between 01/02/2005 00:00:00 And 01/03/2005 00:00:00 includes 01/03/2005 00:00:00


If your table contains dates like 01/03/2005 00:00:00, you can use the DateValue() function to extract the dates for comparison in the query criteria i.e.

SELECT ......
FROM [TableName]
WHERE DateValue([DateTimeField]) Between ...... And ......
.