How do I create a time range criteria in an Access query?

[Guest]

I've inherited a database that works fine for the users. One of the fields
users enter data is the time of the day (Seperate from the date). I need to
create a new report that show how busy employees are based on the time of the
day. I want to create a query in which I can specify a range of times. 8:00
am - 10:00 am. Is it possible to do this? Do I need to put the date and
times together in order to do a between criteria?

 

[Vincent Johns]

I've inherited a database that works fine for the users. One of the fields
users enter data is the time of the day (Seperate from the date). I need to
create a new report that show how busy employees are based on the time of the
day. I want to create a query in which I can specify a range of times. 8:00
am - 10:00 am. Is it possible to do this? Do I need to put the date and
times together in order to do a between criteria?

Short answer: Yes, it's possible.

Are the times that you are comparing with your ranges (such as
8:00-10:00) instants, or are they themselves ranges? For example, do
you have information showing that an employee is busy between 9:30 and
10:15 (which should be counted in 8:00-10:00 and also 10:00-12:00)? Or
do you just count short events that occur during these time periods,
such as that some work was finished at 8:47?

Incidentally, there are functions to separate time from date, to combine
them, and to transform them from one format to another.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[MGFoster]

I've inherited a database that works fine for the users. One of the fields
users enter data is the time of the day (Seperate from the date). I need to
create a new report that show how busy employees are based on the time of the
day. I want to create a query in which I can specify a range of times. 8:00
am - 10:00 am. Is it possible to do this? Do I need to put the date and
times together in order to do a between criteria?

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

You have to include the date in the WHERE clause so you don't get
unusual results. E.g.:

SELECT theDate, theTime
FROM theTable
WHERE theDate = #2/5/2005#
AND theTime BETWEEN #8:00# AND #10:00#
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQ7HO1YechKqOuFEgEQL1TQCfQIOQ7gl/uKYwP6ToBvlgNu78VOsAn2Ld
34NaC1vtj7aWx6oCElrxQbAW
=LKIx
-----END PGP SIGNATURE-----