Assistance needed in date and time fields in a query (1 Viewer)

[Voyager]

Hi Team,
In the attached test db I am having a table with data and a form to choose the criteria for reports and a query which displays the result.
In the tables in_time field I have date in general format in the form when I choose the date field and Shift combo box field (troublesome area) I am having three drop downs day,evening and night.. Day refers to time between 7am to 3pm on the same date, evening refers to 3pm to 11pm on the same day but when comes to night it starts with 11pm on the same date and ends by 7 am on the next day.
In the given form if I choose a date and select a shift the query should display correct results ( in this case 26th night shift should be only 1 record) but I am unable to get it could you assist. I have done lot of workaround and finally collapsed

 

[Ranman256]

i use 2 queries for that 1 late shift,
Q1 gets data 11pm - 11:59:59pm
Q2 gets data 12am - 7am.

 

[BoBaxter]

Why not do something along these lines in VBA to run this?

Private Sub sbmt_Click()
Dim strSQL As String
Dim strtTime As Date
Dim endTime As Date
 
If Forms!JobReport.Form.Controls("Shift") = "Day" Then
    strtTime = Forms!JobReport.Form.Controls("dte") & " 7:00 AM"
    endTime = Forms!JobReport.Form.Controls("dte") & " 3:00 PM"
ElseIf Forms!JobReport.Form.Controls("Shift") = "Evening" Then
    strtTime = Forms!JobReport.Form.Controls("dte") & " 3:00 PM"
    endTime = Forms!JobReport.Form.Controls("dte") & " 11:00 PM"
ElseIf Forms!JobReport.Form.Controls("Shift") = "Night" Then
    strtTime = Forms!JobReport.Form.Controls("dte") & " 11:00 PM"
    endTime = DateAdd("d", 1, Forms!JobReport.Form.Controls("dte")) & " 7:00 AM"
Else
    MsgBox "Please enter the correct shift"
End If
strSQL = "SELECT Jobs.In_time " & _
         "FROM Jobs " & _
         "WHERE (((Jobs.In_time)>=#" & strtTime & "# And (Jobs.In_time)<#" & endTime & "#));"
CurrentDb.QueryDefs("Jobqry").SQL = strSQL
DoCmd.OpenQuery "Jobqry"
End Sub

 

[arnelgp]

Use vba instead of macro. Open the Click event of the botton and change the appropriate time.

 

[Voyager]

Ranman,BoBaxter,arnelgp thanks for your solution. It works like a breeze.
Aside to arnelgp I owe you one.