If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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 |
#2
|
|||
|
|||
"Between" Comparison
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 |
#3
|
|||
|
|||
"Between" Comparison
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 . |
#4
|
|||
|
|||
"Between" Comparison
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 . |
Thread Tools | |
Display Modes | |
|
|