Using 'Between' for filtering dates

[Ellen]

I am using a simple "between 01/01/08 and 01/31/08" criteria in a query. It
is returning records with dates of 01/01/08 and 01/30/08. I've never seen a
query include one date and not the other. (I have seen other query tools
exclude both dates and return only records 'between' the ones listed.)

In order for my query to include 01/31/08 I had to use 'between 01/01/08 and
02/01/08'.

Can anyone explain why this is happening?

 

[Allen Browne]

A date/time field can contain a time as well as a date.
This often happens if the Default Value uses Now() instead of Date().

When there is a time as well as a date, the records on the final date are
not included in the query. That's because Access interprets #1/31/2008# to
be midnight at the start of Jan 31st. Since the time is after than, they are
not included.

The solution is to ask for records less than the next day.
In the Criteria row in query design, use:

 

[akphidelt]

Not sure if this will help but it is good practice to put # around dates. I
always use #s and I've never had a problem like this with Between Dates

So it would look like

Between #1/01/2008# And #1/31/2008#

 

[Ellen]

I did use Between #01/01/2008# and #01/31/2008# - sorry I typed incorrectly
in the message. This produced records including 01/01/08, but excluding
01/31/08. There is another post that explains why this is happening.

 

[Ellen]

thanks so much for the help! I updated my query and it works perfectly.

 

[Rick Brandt]

Not sure if this will help but it is good practice to put # around
dates. I always use #s and I've never had a problem like this with
Between Dates

So it would look like

Between #1/01/2008# And #1/31/2008#

Yes and that will work perfectly if what you actually want is all records
between...

1/01/2008 12:00:00 AM
and
1/31/2008 12:00:00 AM

....because that is what it means when you supply a date with no time specified
(midnight is always assumed). That means all of the records that have a date of
1/31/2008 but have times later in the day are not included.

This is why I never use Between for dates. Even the trick of adding a day to
the end date assumes that there are zero records that actually have midnight for
the time. If any exist on the end date then you will get records on a day that
you didn't want.

More fool-proof is...

SELECT *
FROM SomeTable
WHERE DateField >= #StartDateValueWithNoTime#
AND DateField < DateAdd("d", 1, #EndDateValueWithNoTime#)