"Between" Comparison

[csarjeant via AccessMonster.com]

Quick question, could someone answer for me:
When using the "Between" function to filter records by date, as in Between
[yyyy/m/d] And [yyyy/m/d], do the filtered results include the selected dates?
Or should I use >=, <= instead?
Thanks!

 

[vanderghast]

Between includes the limit, but for the upper limit, that is the day at
MIDNIGHT. If the record has 2001.01.01 00:00:01, then it WON'T be in the
selected ones with:

field BETWEEN start AND #01/01/2001#


since the record is PAST midnight. So, to include the day, the syntax
should be:

field >= start AND field < DateAdd("d", 1, #01/01/2001#)




Vanderghast, Access MVP

 

[Klatuu]

I think you meant your example to be:

field BETWEEN start AND #01/02/2001#

Also, it should be noted that if the date has no time value, then the
BETWEEN will include all records for 01/01/2001. If you are comparing dates,
you have to be sure whether time values are included.
--
Dave Hargis, Microsoft Access MVP

Between includes the limit, but for the upper limit, that is the day at
MIDNIGHT. If the record has 2001.01.01 00:00:01, then it WON'T be in the
selected ones with:

field BETWEEN start AND #01/01/2001#


since the record is PAST midnight. So, to include the day, the syntax
should be:

field >= start AND field < DateAdd("d", 1, #01/01/2001#)




Vanderghast, Access MVP

Quick question, could someone answer for me:
When using the "Between" function to filter records by date, as in Between
[yyyy/m/d] And [yyyy/m/d], do the filtered results include the selected
dates?
Or should I use >=, <= instead?
Thanks!

.

 

[vanderghast]

Indeed, if the field has no time, it has midnight as time, or 00:00:00, if
you prefer.


And since BETWEEN includes the limit, giving "Jan 1, 2001 00:00:00" as
upper limit, it would be include it at midnight (no time), but "Jan 1, 2001
00:00:01" would not be included, since the latter is ocurring AFTER the
upper limit.

So to include UP TO the WHOLE day "Jan 1, 2001", the solution is to include
EXCLUSIVELY up to "Jan 2, 2001":

field >= start AND field < CDate("Jan 2, 2001")

note the strict < , not <= , and the use of the next day (midnight) as
upper limit
and NOT

field BETWEEN start AND CDate("Jan 1, 2001")

which does not include "Jan 1, 2001 00:00:01"
neither

field >= start AND field <= CDate("Jan 2, 2001")

which would include "Jan 2, 2001 00:00:00", ie the second of January at
exact time of midnight.


Vanderghast, Access MVP

I think you meant your example to be:

field BETWEEN start AND #01/02/2001#

Also, it should be noted that if the date has no time value, then the
BETWEEN will include all records for 01/01/2001. If you are comparing
dates,
you have to be sure whether time values are included.
--
Dave Hargis, Microsoft Access MVP

Between includes the limit, but for the upper limit, that is the day at
MIDNIGHT. If the record has 2001.01.01 00:00:01, then it WON'T be in the
selected ones with:

field BETWEEN start AND #01/01/2001#


since the record is PAST midnight. So, to include the day, the syntax
should be:

field >= start AND field < DateAdd("d", 1, #01/01/2001#)




Vanderghast, Access MVP

Quick question, could someone answer for me:
When using the "Between" function to filter records by date, as in
Between
[yyyy/m/d] And [yyyy/m/d], do the filtered results include the selected
dates?
Or should I use >=, <= instead?
Thanks!

.