Criteria for records between two dates

[Guest]

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().

 

[Guest]

Thanks for your response.

Not sure I understand why to use the int of the date, nor is it practical to
keep updating a table.
I think all (my queries and report) results that ask for 1/1/206-1/31/2006
should be getting from one sec am on the 1st to 24:00:00 on the 31'st, e.g.
all values that carry a January as the month. When you mention entering new
dates as Date(), people are just going to enter into the table, to whatever
'format' I gave them.
These have been set to short date, but I think that's just presentation.

Is there a date format or datatype that does not store the time?

<comments>
I thought BETWEEN (particularly) was inclusive and knew to pickup all of
1/31/2006, right up to 23:59:59, before 2/1 clicks in?

Is <=1/31/2006 not going to pick up all the minutes of the 1/31 day either?

And, I saw in my result that >1/31/2006 did pick up a 1/31 date -and- the
minutes in the morning of 1/31. But, that it did work when I asked for

=2/1/2006.

--
MichaelM

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().