Query by 24 hour Shift

[mtech1]

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

[salad]

mt****@yadtel.net wrote:

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

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.

[Saintor]

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

<mt****@yadtel.net> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.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