Most efficient way to check date ranges?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it best to use:
WHERE DATECOL >= #01/01/05# AND DATECOL <= #03/01/05#

Or,

WHERE DATECOL BETWEEN #01/01/05# AND #03/01/05#

Or does it make no difference?
 
HI,


Between rearrange the values:



? EVAL("14 BETWEEN 20 AND 10")
-1


while with the >= AND <=, you have to supply the right limit. Note also
that BETWEEN is always "inclusive", if you need

x >= a AND x < b


you cannot, in general, uses BETWEEN.


Finally, with date, the time is always implicitly supply, here, as midnight.
Since 1:00 AM is AFTER midnight, if your dateCol value is #03/01/2005
01:00:00#, then, that record WON'T be picked. Add 24 hour to get the full
"day" or #03/01/2005#.


For all those reasons, in this case, you may want:

WHERE DATECOL >= #01/01/05# AND DATECOL < 1+ #03/01/05#



where I change the <= to <, and added 1 day (24 hour) to the upper limit.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top