How do I write a "time range" expression to query a field with a .

R

Roger

How do I write a "time range" expression to query a field with a "General
Date" format? I want to select records with a specific time range regardless
of date.
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the TimeValue() function. E.g.:

WHERE TimeValue(date_column) BETWEEN #14:25# And #15:00#

Putting a function on a column usually slows the query down. That's
why, if possible, I put the time value in a separate column.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkCLoYechKqOuFEgEQI4zQCg8sP5pDItr1gIczd8Wxt7AMAcleoAoJb/
awh/cE5jXkrd3a1TqjSH/bhK
=qZOy
-----END PGP SIGNATURE-----
 
J

John Vinson

How do I write a "time range" expression to query a field with a "General
Date" format? I want to select records with a specific time range regardless
of date.
Click to expand...

A Date/Time field (regardless of the format) is stored as a Double
Float count of days and fractions of a day (times). Therefore the
value stored in the table for 5:45 today is a different value than
that which would have been stored for 5:45 yesterday.

Try using a calculated field:

TimeValue([datetimefield])

and a criterion of

BETWEEN #11:30:00# AND #15:30:00#

the # delimiters indicate a date/time criterion. For a parameter query
use

BETWEEN [Enter start time:] AND [Enter end time:]

and use the Query's Parameters collection to specify that these are
Date/Time parameters.

John W. Vinson[MVP]
 
Top