Between Operator Works Differently

[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?

 

[Rob Parker]

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

 

[David Portwood]

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

 

[Pat Hartman \(MVP\)]

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#

 

[David Portwood]

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.