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



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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question