Discussion Locked

Date / Time Query in Access 2000

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have an Access 2000 database that contains records on every call taken by my Help Desk staff in each calendar month. In each record is a single field that identifies the date/time the call was taken. The data is in the following format: 1/2/01 10:30:42 AM

My question is: how do I build a query that is based only on the time with no reference to the date? For example, I may want to query how many calls in the entire database were logged after 8:00 PM or how many were logged between 5:00 pm & 6:00 PM.

Thanks!

7 replies

by dklippert

One suggestion is to use the Hour and Minute functions in the Query. Hour:Hour([Date])

by peyison

Try:

'for calls logged between 5:00 and 6:00 PM (is really between 5:00 and 5:59 PM
Select {field list} From tblTable Where Hour(CallTime) = 17

'for calls logged after 8:00 PM (really 8:00 and after)
Select {field list} From tblTable Where Hour(CallTime) >= 20

Hope this helps.

by amanabala

Try this

SELECT count(*)
FROM Events where FORMAT(EVENT_TIME,'HH:MM')>=[Start Time] and FORMAT(EVENT_TIME,'HH:MM')<= [End Time]

To see how many calls were made during that a particular hour say for between 5 and 6 PM use 17:00 for [Start Timeand 17:59 for [End Time]. For call after a particular hour, say 8 PM till mid night, give 20:00 for [Start Time] and 23:59 for [End Time].

If you want to see the entire night after 8 PM till 6 AM next morning, then just replace the 'AND' in the query with 'OR' and use 05:59 for the [End time]

Hope this helps

This discussion is closed to new replies.