O
Opal
Hi Opal
Well, I was kind of hoping that you would have a go at writing the function
yourself, as I think it would have been a great boost to your
self-confidence, but I'm happy to do it for you if that is what you wish.
So, the pseudo-code logic is to select the shift record where:
(The shift starts and ends on the same day)
AND
(the given time falls between the start and end times)
AND
(ShiftDay matches the given day)
OR
(The shift crosses midnight)
AND
(
(the time is >= ShiftStart AND ShiftDay matches the given day)
OR
(the time is < ShiftEnd AND ShiftDay is one day BEFORE the given day)
)
In SQL, this translates to:
(ShiftStart<ShiftEnd AND GivenTime>=ShiftStart AND GivenTime<ShiftEnd
AND ShiftDay=ThisDay)
OR
(ShiftStart>ShiftEnd AND ((GivenTime>=ShiftStart AND ShiftDay=ThisDay)
OR (GivenTime<ShiftEnd AND ShiftDay=LastDay)))
Translating to a VBA string and substituting our three variables (sTime,
iThisDay and iLastDay) we get:
"(ShiftStart<ShiftEnd AND " & sTime _
& ">=ShiftStart AND " & sTime _
& "<ShiftEnd AND ShiftDay=" & iThisDay _
& ") OR (ShiftStart>ShiftEnd AND ((" & sTime _
& ">=ShiftStart AND ShiftDay=" & iThisDay _
& ") OR (" & sTime & "<ShiftEnd AND ShiftDay=" _
& iLastDay & ")))"
Now, putting the whole lot into our function, we get:
Public Function ShiftFind(ByVal dtFind As Variant) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sTime As String, sSQL As String
Dim iThisDay As Integer, iLastDay As Integer
If IsDate(dtFind) Then
sTime = Format(dtFind, "\#hh:nn:ss\#")
iThisDay = Weekday(dtFind)
iLastDay = ((iThisDay + 5) Mod 7) + 1
Set db = CurrentDb
sSQL = "Select * from TblShift where " _
& "(ShiftStart<ShiftEnd AND " & sTime _
& ">=ShiftStart AND " & sTime _
& "<ShiftEnd AND ShiftDay=" & iThisDay _
& ") OR (ShiftStart>ShiftEnd AND ((" & sTime _
& ">=ShiftStart AND ShiftDay=" & iThisDay _
& ") OR (" & sTime & "<ShiftEnd AND ShiftDay=" _
& iLastDay & ")))"
Set rs = db.OpenRecordset(sSQL, dbOpenForwardOnly)
If rs.RecordCount > 0 Then
ShiftFind = rs!ShiftName
Else
ShiftFind = "<No Shift>"
End If
rs.Close
Else
ShiftFind = "*** Invalid time ***"
End If
End Function
Your data in the table should look something like this:
ShiftDay ShiftName ShiftStart ShiftEnd
2 Shift 1 9:00:00 am 2:00:00 pm
2 Shift 2 2:00:00 pm 4:00:00 pm
2 Shift 3 6:00:00 pm 4:00:00 am
3 Shift 1 9:00:00 am 2:00:00 pm
3 Shift 2 2:00:00 pm 4:00:00 pm
3 Shift 3 6:00:00 pm 4:00:00 am
4 Shift 1 9:00:00 am 2:00:00 pm
4 Shift 2 2:00:00 pm 4:00:00 pm
4 Shift 3 6:00:00 pm 4:00:00 am
5 Shift 1 9:00:00 am 2:00:00 pm
5 Shift 2 2:00:00 pm 4:00:00 pm
5 Shift 3 6:00:00 pm 4:00:00 am
6 Shift 1 9:00:00 am 1:00:00 pm
6 Shift 2 1:00:00 pm 2:00:00 pm
6 Shift 3 4:00:00 pm 2:00:00 am
You can use the following procedure (or a variation of it) to test each hour
for a week and ensure that you are getting the correct result:
Sub TestShiftFind()
Dim i As Integer, dt As Date
For i = 0 To 24 * 7
dt = DateSerial(2008, 4, 13) + TimeSerial(i, 0, 0)
Debug.Print Format(dt, "ddd dd-mmm-yyyy hh:nn"), ShiftFind(dt)
Next
End Sub
Enjoy the fish))
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
[snip]
Thank you Marsh for your continued support on this project.
Graham, Marsh is correct, this is my last issue. As I
have different shift start and end times based on the day
of the week, I need to be able to point the function to
also account for the day of the week when returning the shift
start and end times. I have included a DayofWeek field in
the ShiftTime table to accomodate this and need your assistance
for the function to also take this variable into account when
returning
a shift time.
Thank you Graham.....I just couldn't get my head around
the logic and I really appreciate your assistance.