Distinguishing the time clock am/pm in queries

[Guest]

Hi

Please help.

I am trying to find out when creating a query how to design the query using
a between range to work out the results e.g. find out all the records for the
time range say 10:00 pm and 06:00 am, either on a 12/24 hour clock.

When I use the between range the results fail to find only the records
between that range.

Any ideas?

 

[Guest]

Cna you post the sql, and the field type that you are filtering on

 

[John Spencer (MVP)]

If you are using just the time you need to sets of criteria when you cross midnight.


Between 10Pm and Midnight and Between Midnight and 6AM

Another way to handle this is to adjust the time and then apply criteria to it.
You can still display the original time.

Field: SearchAdjustedTime: DateAdd("h",-6,[YourTimeField])
Criteria: Between #16:00:00# and #23:59:59#

 

[Guest]

Hi thanks for responding.

The datatype in table design is set to date/time, format short time, Imput
Mask 00:00;0;_

Which ever sql i use just don't seem to work. Any suggesions on sql i
should use?

Many thanks

 

[Guest]

You can try

Select * From MyTable Where MyDateFieldName > #22:00# or MyDateFieldName <
#06:00#

it will return all the houres between 10PM and 6AM