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.
"Pat Hartman (MVP)" <please no
(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:46986590$0$30598$(E-Mail Removed)...
>> 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
>>
>> "Rob Parker" <(E-Mail Removed)> wrote in
>> message news:%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:46982800$0$30600$(E-Mail Removed)...
>>>>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?
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Similar Threads
