Between on Date field?

[MorrganMail]

Hi,

I am trying to make a query that returns all records within a specific
time interval. The query targets a Date field containing only a time
i.e. #12/30/1899 TIME#.

WHERE
T.Time Between #06:00:00# And #06:00:00#
=> 0 records

WHERE
T.Time = #06:00:00#
=> 0 records

WHERE
TimeValue(T.Time) Between #06:00:00# And #06:00:00#
=> 1 record #12/30/1899 06:00:00#

Anyone who knows why the first two doesnt work? For me it seems like
there is something fishy about the date part which seems to be !=
12/30/1899.

 

[Guest]

Date and time are stored as a number with the decimal fraction to indicate
the time. Zero point five is 12 noon. Zero. two five ( 0.25 ) is 6 AM.
Zero is 12/30/1899 at midnight - no fraction. When you are querying for time
only you are seeking that date as your day part.

If you want between specified times without regard to date then strip the
date off first like MyTime: Format([YourDateField] - Int([YourDateField]),
"hh:nn:ss")