How can I do a time criteria in a query?

[roaml]

Hello,

Wondering if someone can help me with this problem.

I need to perform a query on specific times of the day. We have three shifts (A, B and C) and I need to pull data for shifts B and C. Shift B is scheduled for 4:00 PM to 12:00 AM and Shift C is scheduled for 12:00 AM to 8:30 AM.

Using a query criteria on my “enter_date and “enter_time”, how can I pull all records for a particular day between 4:00 PM and 8:30 AM?

Thank you much!

 

[JerryKlmns]

roaml,


You need a parameter query. Also Date and Time is a numeric value the integer part of wich represents the date and the rest is the time. So you need all records of a day between 4:00 PM to 12:00 AM and next day's records between 12:00 AM to 8:30 AM.
Copy paste the following to a new query at the SQL view pane

PARAMETERS [Enter Date] DateTime;
SELECT YourTableName.*
FROM YourTableName
WHERE (((Int([DateTimeField]))=[Enter Date]) AND ((IIf(round(CDbl(CDate([DateTimeField])),2)<0.67,False,True))=True)) OR (((Int([DateTimeField]))=[Enter Date]+1) AND ((IIf(round(CDbl(CDate([DateTimeField])),2)<0.35,False,True))=True));

I assume there is only one field (DateTimeField) where you store date and time in your table (YourTableName)
Switch to Design View to see it. Every time you execute this query you 'll get prompt to enter a date.

 

[roaml]

Hi Jerry,

I actually have two seperate fields, one for date and one for time.

I currently have a criteria form extracting data for the dates (between start date and end date) which works well. I would like to extract the data as I am currently doing but extract between specific times.

Thank you for your assistance.

 

[JerryKlmns]

roaml

Because it turns out to be more complicated, you could use a Public function in a module in order to return the according shift

Public Function myShift(myTime As Date) As String
Select Case True
Case CDbl(CDate(myTime)) >= CDbl(CDate("08:30:00")) And CDbl(CDate(myTime)) <= CDbl(CDate("15:59:59"))
myShift = "A"
Case CDbl(CDate(myTime)) >= CDbl(CDate("16:00:00")) And CDbl(CDate(myTime)) <= CDbl(CDate("23:59:59"))
myShift = "B"
Case CDbl(CDate(myTime)) >= CDbl(CDate("00:00:00")) And CDbl(CDate(myTime)) <= CDbl(CDate("08:29:59"))
myShift = "C"
End Select
End Function

Then in the query

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT Table1.*, myShift([TimeField]) AS Shift
FROM Table1
WHERE (((myShift([TimeField]))='b' Or (myShift([TimeField]))='c') AND ((Table1.DateField) Between [Start Date] And [End Date]));