asked on

time related query

I am struggling to figure out what is probably a fairly simple query on a date/time data type field. I need to extract all records, whereby the time section of the date/time, is 'during the night'. So anything between 23:00 (11 PM) and 05:00 (5 AM). Can you help me filter the data please with an example query in MSACCESS? I can do it in Excel with the available filters but ideally want to extract the records from an access DB table.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pau Lo

ASKER

Thanks for the suggestion. I am getting a syntax error (missing operator) though when trying to run it.

SELECT TimeValue([DateCreated]) AS TimeOfApplication
FROM [MyTable]
WHERE TimeValue([DateCreated]) >= #10:00:00 PM" OR TimeValue([DateCreated]) < #05:00:00 AM#

When you execute the query, it highlights the "05" part of the where clause.

this works fine:

SELECT TimeValue([DateCreated]) AS TimeOfApplication
FROM [MyTable]

I cannot see anything obvious.

Pau Lo

ASKER

it was the " after PM

Dale Fye

you had a quote (") rather than a # after PM.

SELECT TimeValue([DateCreated]) AS TimeOfApplication
FROM [MyTable]
WHERE TimeValue([DateCreated]) >= #10:00:00 PM# 
   OR TimeValue([DateCreated]) < #05:00:00 AM#

Open in new window

Pau Lo

ASKER

Thanks, I wasnt aware of the timeValue function before this, it will come in very useful. In case you wanted to edit your solution in case it helps others the " was in your original query.