Just some random thoughts.

One thing to remember while designing queries is that if you stuff a
field with a Now() value (date and time), it is not the same as a Date()
stuff (date and time of 00:00:00).  Let's say you want to check for
something between March 1 and March 2.  Using dates only, you can query
on that.  If you use time, you need to specify times also.  For example,
if you asked to get something between March 1 and March 2, it would only
 retrieve those records between March 1 at 00:00:00 and March 2 at
00:00:00.

You might want to add an extra field, called DateOfShift.  Sure, you can
calc out the date from your date/time field, but someone may start their
second shift after midnight and could be excluded.  So stuff DateOfShift
with the date (no time).  Less calcs later on. Thus you could query from
records with a start date of March 2 and then by shift category, and
then start date/time.

I was faced to the same issue.  I added another field for the date of start
of the shift which is populated by a function (that could be simplified
again)

Public Function DateStartShUpd(sh As String, DateStart As Date) As Date

On Error GoTo err_DateStartShUpd

Dim ShEnd As Date, DayNo As Integer, d0 As Date, HrBegin As Date, UpdStatus
As Integer, UpdStatusInfo As String

DayNo = WeekDay(DateStart)

Select Case WeekDay(DateStart)
Case 1
   HrBegin = Nz(DLookup("sSundayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")

Case 2
   HrBegin = Nz(DLookup("sMondayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")

Case 3
   HrBegin = Nz(DLookup("sTuesdayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")

Case 4
   HrBegin = Nz(DLookup("sWednesdayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")

Case 5
   HrBegin = Nz(DLookup("sThursdayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")

Case 6
   HrBegin = Nz(DLookup("sFridayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")

Case 7
   HrBegin = Nz(DLookup("sSaturdayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")

End Select

d0 = Format(DateStart, "yyyy-mm-dd")

If Format(HrBegin, "hh:nn") <= Format(DateStart, "hh:nn") Then
   DateStartShUpd = d0
Else
   DateStartShUpd = DateAdd("d", -1, d0)
End If

exit_DateStartShUpd:

Exit Function

err_DateStartShUpd:
If Err.Number = 94 Then
   MsgBox Err.Number & " " & Err.Description
   'MsgBox "Work shift starting hour or duration invalid."
   Resume Next
   'Exit Function
End If

End Function