General discussion

Locked

Date / Time Query in Access 2000

By B.Hughes ·
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!

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Date / Time Query in Access 2000

by DKlippert In reply to Date / Time Query in Acce ...

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

Collapse -

Date / Time Query in Access 2000

by B.Hughes In reply to Date / Time Query in Acce ...

Poster rated this answer

Collapse -

Date / Time Query in Access 2000

by Peyison In reply to Date / Time Query in Acce ...

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.

Collapse -

Date / Time Query in Access 2000

by B.Hughes In reply to Date / Time Query in Acce ...

Poster rated this answer

Collapse -

Date / Time Query in Access 2000

by amanabala In reply to Date / Time Query in Acce ...

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

Collapse -

Date / Time Query in Access 2000

by B.Hughes In reply to Date / Time Query in Acce ...

Poster rated this answer

Collapse -

Date / Time Query in Access 2000

by B.Hughes In reply to Date / Time Query in Acce ...

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums