Search between times.

[Floyd Forbes]

I have column base on time. How do I search between 6:00:00 PM and 5:00:00
AM?
Search time will not go pass midnight, how can I fix this problem?

 

[John Vinson]

I have column base on time. How do I search between 6:00:00 PM and 5:00:00
AM?
Search time will not go pass midnight, how can I fix this problem?

If this is a pure-time value, you'll need to use two criteria: later
than 6pm, OR earlier than 5am.

An Access Date/Time value is stored as a double float number, a count
of days and fractions of a day (times) since midnight, December 30,
1899. If you're storing *just* times in this field, you'll have this
kind of "spanning midnight" problem. You may want to consider storing
the date AND time in the same field - the range #08/30/2006 18:00:00#
to #08/31/2006 05:00:00# works perfectly well and has no ambiguity
about which date you mean.

John W. Vinson[MVP]

 

[Rick Brandt]

I have column base on time. How do I search between 6:00:00 PM and
5:00:00 AM?
Search time will not go pass midnight, how can I fix this problem?

When you are "only storing time" in Access you are actually storing time on the
date 12/30/1899 so 5 AM is always before 6 PM. It does not represent 5 AM on
the following morning so you cannot use the BETWEEN predicate.

You could use criteria...

WHERE TimeField <=#5:00:00 AM#
Or TimeField >=#6:00:00 PM#