Look up between 2 times over midnight - is there an easier way than recordset code?

[Opal]

Hi Opal

Thanks for the clarification (and to Marsh also

Is the purpose of the timer event to update a real-time clock on the form,
showing the day, date, time, and the current shift?

I was a little bit concerned by the line that says:
   txtDayofWeek = DatePart("w", txtTodaysDate)

This suggests that you are using unbound textboxes to display date and time
information where It would be easier to use calculated textboxes.

For example, the current day and date could be displayed in a single textbox
with the following properties:
   ControlSource: =Date()
   Format: dddd, d mmmm yyyy

This format will display like this:
   Friday, 11 April 2008
You can set the format differently as you choose.

Similarly, the time can be displayed with a ControlSource of =Now() andan
appropriate format.

Finally, using your special function, the current shift can be displayed as:
   =ShiftFind(Now())

There is no need to pass it the day of the week, because this is easily
ascertained from the date which has been passed to the function.

Remember that the logic of the previous function (ignoring the day of the
week) was to find the matching record (if any) where:

(The shift starts and ends on the same day)
AND
(the given time falls between the start and end times)

  OR

(The shift crosses midnight)
AND
 (
   (the time is >= ShiftStart)
   OR
   (the time is < ShiftEnd)
 )

In SQL, this translated to:

(ShiftStart<ShiftEnd AND GivenTime>=ShiftStart AND GivenTime<ShiftEnd)
OR
(ShiftStart>ShiftEnd AND (GivenTime>=ShiftStart OR GivenTime<ShiftEnd))

So in VBA, if the string sTime contains the given time enclosed in #-signs,
you can construct this WHERE-clause as follows:

"(ShiftStart<ShiftEnd AND " & sTime & ">=ShiftStart AND " & sTime _
& "<ShiftEnd) OR (ShiftStart>ShiftEnd AND (" & sTime & ">=ShiftStart OR" _
& sTime & "<ShiftEnd))"

Note that the whole SQL string has been enclosed in quotes and every
occurrence of /GivenTime/ has been replaced by /" & sTime & "/.

Now, using the old principle that "if you give a man a fish you will feed
him for a day, but if you teach a man to fish you will feed him for a
lifetime", I am challenging you to modify the SQL code and then the VBA code
to take into account the day of the week number.

The modified logic is:

(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)
 )

Remember that:

WeekDay(SomeDate) gives the day of the week number (1-7) for the given
date/time.
For example, Weekday(Now) returns 5 (Thursday).

The day of the week number for the day BEFORE a given date is calculated by:
((WeekDay(SomeDate)+5) Mod 7) + 1

I suggest you add two variables to the function:
   Dim iThisDay as Integer
   Dim iLastDay as Integer
and calculate their values as follows:
   iThisDay = WeekDay(dtFind)
   iLastDay = ((iThisDay + 5) Mod 7) + 1

You can then substitute these variables into your SQL string in the same way
as you do for sTime.

Graham.

The reason for not using control expressions is because some
of the expressions got more than a little messy with nested
IIf, etc.  Since control expression recalculations and the
timer event calculations are asynchronous, Opal's testing
sometimes produced strange/inconsistent results.  The only
way to keep all these calculations in sync is do them all
either in control source expressions or all in a VBA (timer
event) procedure.

I'm pretty sure that the only difference is that your
procedure would be called from the timer event instead of
from a text box expression.  Note that the timer interval
only needs to be 60000 (one minute) so the amount of code is
not critical in this case.

Opal's other thread where we waded through these issues has
subject: Code to auto fill txt/cbo boxes and started 29 Mar
2008.  I will be leaving very early tomorrow morning so I
hope you can follow up on what, at this point, looks like
Opal's last(?) issue in these two threads.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

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.

 

[Graham Mandeno]

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.

 

[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.