Criteria for records between two dates

[Michael Miller]

I need a refresher on the rules of the following, independent query criteria
pieces:
I need to be sure I am getting an entire month or cutting off a month cleanly.

BETWEEN date1 AND date2 (I couldn't find BETWEEN listed anywhere in Access
and Access VBA help, but it's my understanding that it's the two dates
inclusive, but don't know how it treats the minutes and seconds)

and

= date1 AND <= date2 and
<date1
and
date1

I just ran a query with a search for an EndDate >#1/31/2006, and it brought
up one record dated 1/31/2006. It's a datetime Access field, formatted in
the query property to show a Short date. I'm thinking that >1/31 starts 1
sec after midnight.
The date pulled was: 1/31/2006 11:00:10 AM

Do I have to use >=2/1/2006?

Thanks.

 

[fredg]

Your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 11/24/2005 enter 11/25/2005.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then Change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = int([DateField]);

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().