Using 'Between' for filtering dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ellen - 07 Feb 2008 01:54 GMT

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?

Reply to this Message

Allen Browne - 07 Feb 2008 02:00 GMT

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:
>= #1/1/2008# And < #2/1/2008#

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I am using a simple "between 01/01/08 and 01/31/08" criteria in a query.
>It
[quoted text clipped - 8 lines]
>
> Can anyone explain why this is happening?

Reply to this Message

Ellen - 07 Feb 2008 02:13 GMT

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

> 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().
[quoted text clipped - 20 lines]
> >
> > Can anyone explain why this is happening?

Reply to this Message

akphidelt - 07 Feb 2008 02:03 GMT

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#

> 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
[quoted text clipped - 5 lines]
>
> Can anyone explain why this is happening?

Reply to this Message

Ellen - 07 Feb 2008 02:12 GMT

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.

> 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
[quoted text clipped - 12 lines]
> >
> > Can anyone explain why this is happening?

Reply to this Message

Rick Brandt - 07 Feb 2008 03:28 GMT

> 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
[quoted text clipped - 3 lines]
>
> 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#)

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message