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_DateStartSh Upd
Dim ShEnd As Date, DayNo As Integer, d0 As Date, HrBegin As Date, UpdStatus
As Integer, UpdStatusInfo As String
DayNo = WeekDay(DateSta rt)
Select Case WeekDay(DateSta rt)
Case 1
HrBegin = Nz(DLookup("sSu ndayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")
Case 2
HrBegin = Nz(DLookup("sMo ndayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")
Case 3
HrBegin = Nz(DLookup("sTu esdayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")
Case 4
HrBegin = Nz(DLookup("sWe dnesdayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")
Case 5
HrBegin = Nz(DLookup("sTh ursdayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")
Case 6
HrBegin = Nz(DLookup("sFr idayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")
Case 7
HrBegin = Nz(DLookup("sSa turdayBegin", "tblShift", "ShiftNo='" & sh &
"'"), "")
End Select
d0 = Format(DateStar t, "yyyy-mm-dd")
If Format(HrBegin, "hh:nn") <= Format(DateStar t, "hh:nn") Then
DateStartShUpd = d0
Else
DateStartShUpd = DateAdd("d", -1, d0)
End If
exit_DateStartS hUpd:
Exit Function
err_DateStartSh Upd:
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
<mt****@yadtel. net> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.com.. .
Access 2003
I need to create a query to view data by 3 - 24 hour shifts. Ex: There
are 3 shifts, each shift is 24 hours starting at 0700 through 0700.
Each shift is designated as A, B, or C. 01/01/2006 is an "A" shift.
The data does have a date field that contains both date and time.
Could anyone point me in the right direction to get started?
Any and All help truly appreciated!
Dale