"Between" Comparison

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!

--
Message posted via http://www.accessmonster.com

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




"csarjeant via AccessMonster.com" u55302@uwe wrote in message
news:9debdc547960a@uwe...
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!

--
Message posted via http://www.accessmonster.com

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


"vanderghast" wrote:

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




"csarjeant via AccessMonster.com" u55302@uwe wrote in message
news:9debdc547960a@uwe...
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!

--
Message posted via http://www.accessmonster.com


.

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


"Klatuu" wrote in message
...
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


"vanderghast" wrote:

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




"csarjeant via AccessMonster.com" u55302@uwe wrote in message
news:9debdc547960a@uwe...
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!

--
Message posted via http://www.accessmonster.com


.