Between Operator Works Differently

  • Thread starter Thread starter David Portwood
  • Start date Start date
D

David Portwood

I have two reports for which I construct SELECT statements as their record
source. The two SELECT statements are different but each has a date field
and each statement includes a WHERE clause in which I set a range on the
date field using "Between #Date1# And #Date2#".

For one statement, the Between is inclusive, e.g., Between #7/13/07# and
#7/13/07# finds records with Date = #7/13/07#. For the other, it is
exclusive. In order to find a record dated #7/13/07# I have to code Between
#7/12/07# And #7/14/07#.

This is driving me crazy. Any idea what this is about?
 
Hi David,

I suspect that your two SELECT statements are using different tables; in
one, the date field contains only the date portion of the datetime entity,
in the other, it also contains a time element. Datetime datatypes store the
date as the integer portion of a double precision number (as days since
30/12/1899), and the time as the decimal portion (as a proportion of 24
hours). Since you are entering only a date in your Between crierion, you
will be looking for values up to (for example) 39276; in the first instance
you mention, a date of 7/13/07 will have a value of 39276 and will be found,
while in the second a date with a time component will have a value of
32976.27583, and is greater than the value you have entered in your Between
criterion, and will (rightly) be excluded.

A likely cause of the difference in the underlying data is that, in the
first case, the datetime values is populated via a Date() function, while in
the second it is populated by a Now() function (which includes the time
element).

HTH,

Rob
 
Makes sense. My two SELECT statements are using different tables. I don't
know how I would be getting a time element into one of the tables because as
far as I can recall I don't use time elements anywhere. However, I know now
that Now() includes a time element but I may not have been aware of this
when I first began coding my app and designing my tables.

I'll check this on Monday. Thanks very much, Rob.

David Portwood
 
You can solve the problem by using the DateValue() function. That allows
you to keep the time element but still qualify on only the date.
Where DateValue(YourDate) Between #7/13/07# and #7/13/07#
 
Rob was right. I had a Now() reference from early days that slipped through
the crack. I'll keep your solution in mind. I'm sure similar situations will
occur.
 
Back
Top