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

[Evi]

I wanted to find out which factory shift, a particular time (Now()) falls
into


TblShift has

ShiftName,-ShiftStart(time), ShiftEnd(Time)

Shift1- 09:00:00 -14:00:00

Shift2 - 14:00:01 - 16:00:00

Shift3 - 18:00:01 - 04:00:00



So if Now() = 14:30, I want the result to be "Shift2"

because 14:30 is between 14:00: and 16:00



If Now() = 08:00, I want it to read "No Shift"



Dlookup between ShiftStart and ShiftEnd worked until Midnight struck, then I
got a "No Shift" because Now() at 01:00 was no longer greater than or equal
to ShiftStart and less than or equal To ShiftEnd - it was less than both
these values.



I couldn't figure out an IIF to sift this out.



To make this work, I've added a dummy date to the shift times 25/12/2008
with the Over-midnight shiftEnd being 26/12/08

I've formatted them as General Date

This function does the trick but was there an easier way without using
Recordset (which doesn't always translate easily over different versions of
Access)?



(I've left the dtFind variant there so that the function can be used say to
look up a worker's shift by using the time he started work)



Function ShiftFind(ByVal dtFind As Variant) As String

Dim dtNow As Date

Dim Dummydate As Date

Dim MyFindField As String

Dim MyStartField As String

Dim MyEndField As String

Dim MyTable As String

Dim MyStart As Date

Dim MyEnd As Date

Dim MyFind As String

Dim rs As Recordset

Dim a As Integer



If Not IsNull(dtFind) Then

'change these names to match your Table

MyStartField = "ShiftStart"

MyEndField = "ShiftEnd"

MyFindField = "ShiftName"

MyTable = "QryShiftTest"

'Query is sorted by StartTime



Dummydate = #12/25/2008#

dtNow = Dummydate + TimeValue(dtFind)



Set rs = CurrentDb.OpenRecordset(MyTable)



rs.MoveFirst





Do Until rs.EOF

MyStart = rs(MyStartField)

MyEnd = rs(MyEndField)

MyFind = rs(MyFindField)



If TimeValue(MyEnd) < TimeValue(MyStart) And dtNow < MyStart Then



'ie shift spans midnight and dtNow is after midnight

'add 1 day to dtNow



dtNow = dtNow + 1



Else

dtNow = dtNow

End If



If dtNow >= MyStart And dtNow <= MyEnd Then

ShiftFind = MyFind

GoTo FoundIt

'break out of loop

Else

rs.MoveNext

'it wasn't the right time so move to next record

End If





Loop

ShiftFind = "No shift"

End If

FoundIt:

rs.Close

Set rs = Nothing





End Function



Evi

 

[Opal]

I wanted to find out which factory shift, a particular time (Now()) falls
into

TblShift has

ShiftName,-ShiftStart(time), ShiftEnd(Time)

Shift1- 09:00:00 -14:00:00

Shift2 - 14:00:01 - 16:00:00

Shift3 - 18:00:01 -  04:00:00

So if Now() = 14:30, I want the result to be "Shift2"

because 14:30 is between 14:00: and 16:00

If Now() = 08:00, I want it to read "No Shift"

Dlookup between ShiftStart and ShiftEnd worked until Midnight struck, thenI
got a "No Shift"  because Now() at 01:00 was no longer greater than or equal
to ShiftStart and less than or equal To ShiftEnd - it was less than both
these values.

I couldn't figure out an IIF to sift this out.

To make this work, I've added a dummy date to the shift times 25/12/2008
with the Over-midnight shiftEnd being 26/12/08

I've formatted them as General Date

This function does the trick but was there an easier way without using
Recordset (which doesn't always translate easily over different versions of
Access)?

(I've left the dtFind variant there so that the function can be used say to
look up a worker's shift by using the time he started work)

Function ShiftFind(ByVal dtFind As Variant) As String

Dim dtNow As Date

Dim Dummydate As Date

Dim MyFindField As String

Dim MyStartField As String

Dim MyEndField As String

Dim MyTable As String

Dim MyStart As Date

Dim MyEnd As Date

Dim MyFind As String

Dim rs As Recordset

Dim a As Integer

If Not IsNull(dtFind) Then

'change these names to match your Table

    MyStartField = "ShiftStart"

    MyEndField = "ShiftEnd"

    MyFindField = "ShiftName"

    MyTable = "QryShiftTest"

    'Query is sorted by StartTime

        Dummydate = #12/25/2008#

    dtNow = Dummydate + TimeValue(dtFind)

    Set rs = CurrentDb.OpenRecordset(MyTable)

     rs.MoveFirst

Do Until rs.EOF

        MyStart = rs(MyStartField)

        MyEnd = rs(MyEndField)

        MyFind = rs(MyFindField)

        If TimeValue(MyEnd) < TimeValue(MyStart) And dtNow < MyStart Then

         'ie shift spans midnight and dtNow is after midnight

          'add 1 day to dtNow

         dtNow = dtNow + 1

        Else

            dtNow = dtNow

        End If

        If dtNow >= MyStart And dtNow <= MyEnd Then

            ShiftFind = MyFind

            GoTo FoundIt

            'break out of loop

        Else

          rs.MoveNext

          'it wasn't the right time so move to next record

        End If

 Loop

    ShiftFind = "No shift"

End If

FoundIt:

rs.Close

 Set rs = Nothing

End Function

 Evi

Hi Evi,

Having trouble with:

MyStart = rs(MyStartField)


MyEnd = rs(MyEndField)


MyFind = rs(MyFindField)


"Item not found in this collection"

My VBA is still on a sharp learning curve....what are these
lines trying to accomplish?

 

[Graham Mandeno]

Hi Evi

If the shift starts and ends on the same day (ShiftStart<ShiftEnd) then the
time will need to be between ShiftStart and ShiftEnd.

If ShiftEnd is earlier than ShiftStart, then the shift crosses midnight and
the logic needs to be reversed. The given time must be greater than
ShiftEnd OR earlier than ShiftStart.

Putting all that together, you can use a function like this:

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
If IsDate(dtFind) Then
sTime = Format(dtFind, "\#hh:nn:ss\#")
Set db = CurrentDb
sSQL = "Select * from TblShift where (" _
& sTime & ">=ShiftStart And " _
& sTime & "<ShiftEnd And ShiftStart<ShiftEnd) OR ((" _
& sTime & "<ShiftEnd Or " _
& sTime & ">=ShiftStart) And ShiftStart>ShiftEnd)"
Set rs = db.OpenRecordset(sSQL, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ShiftFind = "<No Shift>"
Else
ShiftFind = rs!ShiftName
End If
rs.Close
Else
ShiftFind = "*** Invalid time ***"
End If
End Function

You could use the WHERE clause you have constructed in a DLookup if you
prefer, but opening a recordset this way is faster.

 

[Evi]

I wanted to find out which factory shift, a particular time (Now()) falls
into

TblShift has

ShiftName,-ShiftStart(time), ShiftEnd(Time)

Shift1- 09:00:00 -14:00:00

Shift2 - 14:00:01 - 16:00:00

Shift3 - 18:00:01 - 04:00:00

So if Now() = 14:30, I want the result to be "Shift2"

because 14:30 is between 14:00: and 16:00

If Now() = 08:00, I want it to read "No Shift"

Dlookup between ShiftStart and ShiftEnd worked until Midnight struck, then I
got a "No Shift" because Now() at 01:00 was no longer greater than or equal
to ShiftStart and less than or equal To ShiftEnd - it was less than both
these values.

I couldn't figure out an IIF to sift this out.

To make this work, I've added a dummy date to the shift times 25/12/2008
with the Over-midnight shiftEnd being 26/12/08

I've formatted them as General Date

This function does the trick but was there an easier way without using
Recordset (which doesn't always translate easily over different versions of
Access)?

(I've left the dtFind variant there so that the function can be used say to
look up a worker's shift by using the time he started work)

Function ShiftFind(ByVal dtFind As Variant) As String

Dim dtNow As Date

Dim Dummydate As Date

Dim MyFindField As String

Dim MyStartField As String

Dim MyEndField As String

Dim MyTable As String

Dim MyStart As Date

Dim MyEnd As Date

Dim MyFind As String

Dim rs As Recordset

Dim a As Integer

If Not IsNull(dtFind) Then

'change these names to match your Table

MyStartField = "ShiftStart"

MyEndField = "ShiftEnd"

MyFindField = "ShiftName"

MyTable = "QryShiftTest"

'Query is sorted by StartTime

Dummydate = #12/25/2008#

dtNow = Dummydate + TimeValue(dtFind)

Set rs = CurrentDb.OpenRecordset(MyTable)

rs.MoveFirst

Do Until rs.EOF

MyStart = rs(MyStartField)

MyEnd = rs(MyEndField)

MyFind = rs(MyFindField)

If TimeValue(MyEnd) < TimeValue(MyStart) And dtNow < MyStart Then

'ie shift spans midnight and dtNow is after midnight

'add 1 day to dtNow

dtNow = dtNow + 1

Else

dtNow = dtNow

End If

If dtNow >= MyStart And dtNow <= MyEnd Then

ShiftFind = MyFind

GoTo FoundIt

'break out of loop

Else

rs.MoveNext

'it wasn't the right time so move to next record

End If

Loop

ShiftFind = "No shift"

End If

FoundIt:

rs.Close

Set rs = Nothing

End Function

Evi

Hi Evi,

Having trouble with:

MyStart = rs(MyStartField)


MyEnd = rs(MyEndField)


MyFind = rs(MyFindField)


"Item not found in this collection"

My VBA is still on a sharp learning curve....what are these
lines trying to accomplish?

Hi Opal

It certainly is a steep learning curve.

What version of Access do you have?

Change the line which says
Dim rs As Recordset
to
Dim rs As DAO.Recordset

Open your module and go to Tools and References. If you see the word Missing
next to a Reference, make a note of the full name of that reference. Untick
it. Click OK. Reopen references and if it is not ticked yet, scroll down the
list, find it and tick it.

Look for a Reference called Microsoft DAO 3.6 Object Library. If it isn't in
the list, you may be able to find it by clicking the Browse button and
looking for dao360.dll - it's usually in C:\Program Files\Common
Files\Microsoft Shared\DAO\ but if it isn't there do a Find Files and see if
you have it.
If you have a Reference which mentions Microsoft ADO, then (unless your
database contains other Recordset code in its modules) move DAO just above
ADO.

Debug the code and if you still get a problem, write down what References
you do have in that box in your next email.

MyStart, MyEnd and all the bits which came in the Dim area are variables.
These are like x in algebra, a variable can stand for anything
MyStart = rs(MyStartField)


MyEnd = rs(MyEndField)


MyFind = rs(MyFindField)
Those lines are saying eg
Until I say differently, MyStart = Whatever is in current record in
QryShiftTest in the Field called ShiftStart

In the bit just under 'change these names to match your Table
are the names of those fields. Change the strings to match the names of the
fields in your db


Evi

 

[Opal]

Hi Evi,

Having trouble with:

        MyStart = rs(MyStartField)

        MyEnd = rs(MyEndField)

        MyFind = rs(MyFindField)

"Item not found in this collection"

My VBA is still on a sharp learning curve....what are these
lines trying to accomplish?

Hi Opal

It certainly is a steep learning curve.

What version of Access do you have?

Change the line which says
Dim rs As Recordset
to
Dim rs As DAO.Recordset

Open your module and go to Tools and References. If you see the word Missing
next to a Reference, make a note of the full name of that reference. Untick
it. Click OK. Reopen references and if it is not ticked yet, scroll down the
list, find it and tick it.

Look for a Reference called Microsoft DAO 3.6 Object Library. If it isn't in
the list, you may be able to find it by clicking the Browse button and
looking for dao360.dll - it's usually in C:\Program Files\Common
Files\Microsoft Shared\DAO\ but if it isn't there do a Find Files and see if
you have it.
If you have a Reference which mentions Microsoft ADO, then (unless your
database contains other Recordset code in its modules) move DAO just above
ADO.

Debug the code and if you still get a problem, write down what References
you do have in that box in your next email.

MyStart, MyEnd and all the bits which came in the Dim area are variables.
These are like x in algebra, a variable can stand for anything
       MyStart = rs(MyStartField)

        MyEnd = rs(MyEndField)

        MyFind = rs(MyFindField)
Those lines are saying eg
Until I say differently, MyStart = Whatever is in current record in
QryShiftTest in the Field called ShiftStart

In the bit just under 'change these names to match your Table
are the names of those fields. Change the strings to match the names of the
fields in your db

Evi- Hide quoted text -

- Show quoted text -

Hi Evi,

I am running Access 2003

Under references the Microsoft DAO 3.6 Object Library is checked.

He is the code as I have it in my database:

Function ShiftFind(ByVal dtFind As Variant) As String

Dim dtNow As Date
Dim Dummydate As Date
Dim MyFindField As String
Dim MyStartField As String
Dim MyEndField As String
Dim MyTable As String
Dim MyStart As Date
Dim MyEnd As Date
Dim MyFind As String
Dim rs As DAO.Recordset
Dim a As Integer

If Not IsNull(dtFind) Then

'change these names to match your Table

MyStartField = "StartofShift"
MyEndField = "EndofShift"
MyFindField = "ShiftNameID"
MyTable = "ShiftName"

'Query is sorted by StartTime

Dummydate = #12/25/2008#

dtNow = Dummydate + TimeValue(dtFind)
Set rs = CurrentDb.OpenRecordset(MyTable)

rs.MoveFirst

Do Until rs.EOF

MyStartField = rs(MyStartField)
MyEndField = rs(MyEndField)
MyFindField = rs(MyFindField)

If TimeValue(MyEnd) < TimeValue(MyStart) And dtNow < MyStart
Then

'ie shift spans midnight and dtNow is after midnight

'add 1 day to dtNow

dtNow = dtNow + 1

Else

dtNow = dtNow

End If

If dtNow >= MyStart And dtNow <= MyEnd Then

ShiftFind = MyFind

GoTo FoundIt
'break out of loop
Else

rs.MoveNext

'it wasn't the right time so move to next record

End If
Loop

ShiftFind = "No shift"

End If

FoundIt:

rs.Close

Set rs = Nothing

End Function


I am still getting the same error

 

[Evi]

It's finding everything as No Shift Graham and I really don't see why. It
looks perfectly logical to me. The field names are fine.

What does dbOpenForwardOnly do? I've never seen that before and

Is there a reason for using
set currentdb?
I notice most people do it but as CurrentDb is often only used once in the
code, why do so many people bother to set it as a variable?

The reason I wanted to avoid the Recordset code is below in Opal's message.
The function I have does work on my PC (Acc2000) but Opal (Acc2003) is
getting an error message which may be to do with references.
Evi

Hi Evi

If the shift starts and ends on the same day (ShiftStart<ShiftEnd) then the
time will need to be between ShiftStart and ShiftEnd.

If ShiftEnd is earlier than ShiftStart, then the shift crosses midnight and
the logic needs to be reversed. The given time must be greater than
ShiftEnd OR earlier than ShiftStart.

Putting all that together, you can use a function like this:

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
If IsDate(dtFind) Then
sTime = Format(dtFind, "\#hh:nn:ss\#")
Set db = CurrentDb
sSQL = "Select * from TblShift where (" _
& sTime & ">=ShiftStart And " _
& sTime & "<ShiftEnd And ShiftStart<ShiftEnd) OR ((" _
& sTime & "<ShiftEnd Or " _
& sTime & ">=ShiftStart) And ShiftStart>ShiftEnd)"
Set rs = db.OpenRecordset(sSQL, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ShiftFind = "<No Shift>"
Else
ShiftFind = rs!ShiftName
End If
rs.Close
Else
ShiftFind = "*** Invalid time ***"
End If
End Function

You could use the WHERE clause you have constructed in a DLookup if you
prefer, but opening a recordset this way is faster.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I wanted to find out which factory shift, a particular time (Now()) falls
into


TblShift has

ShiftName,-ShiftStart(time), ShiftEnd(Time)

Shift1- 09:00:00 -14:00:00

Shift2 - 14:00:01 - 16:00:00

Shift3 - 18:00:01 - 04:00:00



So if Now() = 14:30, I want the result to be "Shift2"

because 14:30 is between 14:00: and 16:00



If Now() = 08:00, I want it to read "No Shift"



Dlookup between ShiftStart and ShiftEnd worked until Midnight struck, then
I
got a "No Shift" because Now() at 01:00 was no longer greater than or
equal
to ShiftStart and less than or equal To ShiftEnd - it was less than both
these values.



I couldn't figure out an IIF to sift this out.



To make this work, I've added a dummy date to the shift times 25/12/2008
with the Over-midnight shiftEnd being 26/12/08

I've formatted them as General Date

This function does the trick but was there an easier way without using
Recordset (which doesn't always translate easily over different versions
of
Access)?



(I've left the dtFind variant there so that the function can be used say
to
look up a worker's shift by using the time he started work)



Function ShiftFind(ByVal dtFind As Variant) As String

Dim dtNow As Date

Dim Dummydate As Date

Dim MyFindField As String

Dim MyStartField As String

Dim MyEndField As String

Dim MyTable As String

Dim MyStart As Date

Dim MyEnd As Date

Dim MyFind As String

Dim rs As Recordset

Dim a As Integer



If Not IsNull(dtFind) Then

'change these names to match your Table

MyStartField = "ShiftStart"

MyEndField = "ShiftEnd"

MyFindField = "ShiftName"

MyTable = "QryShiftTest"

'Query is sorted by StartTime



Dummydate = #12/25/2008#

dtNow = Dummydate + TimeValue(dtFind)



Set rs = CurrentDb.OpenRecordset(MyTable)



rs.MoveFirst





Do Until rs.EOF

MyStart = rs(MyStartField)

MyEnd = rs(MyEndField)

MyFind = rs(MyFindField)



If TimeValue(MyEnd) < TimeValue(MyStart) And dtNow < MyStart Then



'ie shift spans midnight and dtNow is after midnight

'add 1 day to dtNow



dtNow = dtNow + 1



Else

dtNow = dtNow

End If



If dtNow >= MyStart And dtNow <= MyEnd Then

ShiftFind = MyFind

GoTo FoundIt

'break out of loop

Else

rs.MoveNext

'it wasn't the right time so move to next record

End If





Loop

ShiftFind = "No shift"

End If

FoundIt:

rs.Close

Set rs = Nothing





End Function



Evi

 

[Graham Mandeno]

Hi Evi

What does dbOpenForwardOnly do? I've never seen that before and

It opens a forward-only snapshot. It is much faster and more efficient to
open a recordset that is read-only and not updated by other users' changes
(a snapshot) which is only read in one direction, with no ability to search
or move backwards (forward-only).

Is there a reason for using
set currentdb?

This is not strictly necessary in this case. You can actually just say:
Set rs = CurrentDb.OpenRecordset(...)
However, there are many more cases when referring to CurrentDb directly does
not work as expected, and it's therefore good practice (IMHO) always to
assign it to a local variable.

It's finding everything as No Shift Graham and I really don't see why. It
looks perfectly logical to me. The field names are fine.

That is strange. I have just created a test table with exactly your data
and it appears to work fine. Does it return *everything* as <No Shift> or
just the boundary values (on the shift change times)?

You could try copying the value of sSQL and pasting it into a the SQL window
of a new query. That might indicate where the problem lies when you try to
run it.

The reason I wanted to avoid the Recordset code is below in Opal's
message.
The function I have does work on my PC (Acc2000) but Opal (Acc2003) is
getting an error message which may be to do with references.

As I said, you can build just the WHERE string (omit the "Select * from
TblShift where " part) and use that in a DLookup. However, opening a
recordset is faster.

Every version of Access has DAO. The problem is that for one or two
versions (2000 and 2002 I think) the reference was not included by default
in new databases.

Sorry, Evi. I didn't realise till later that Opal, not you, was the
original poster. I hope he/she has seen my post and tried the code.
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

It's finding everything as No Shift Graham and I really don't see why. It
looks perfectly logical to me. The field names are fine.

What does dbOpenForwardOnly do? I've never seen that before and

Is there a reason for using
set currentdb?
I notice most people do it but as CurrentDb is often only used once in the
code, why do so many people bother to set it as a variable?

The reason I wanted to avoid the Recordset code is below in Opal's
message.
The function I have does work on my PC (Acc2000) but Opal (Acc2003) is
getting an error message which may be to do with references.
Evi

Hi Evi

If the shift starts and ends on the same day (ShiftStart<ShiftEnd) then the
time will need to be between ShiftStart and ShiftEnd.

If ShiftEnd is earlier than ShiftStart, then the shift crosses midnight and
the logic needs to be reversed. The given time must be greater than
ShiftEnd OR earlier than ShiftStart.

Putting all that together, you can use a function like this:

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
If IsDate(dtFind) Then
sTime = Format(dtFind, "\#hh:nn:ss\#")
Set db = CurrentDb
sSQL = "Select * from TblShift where (" _
& sTime & ">=ShiftStart And " _
& sTime & "<ShiftEnd And ShiftStart<ShiftEnd) OR ((" _
& sTime & "<ShiftEnd Or " _
& sTime & ">=ShiftStart) And ShiftStart>ShiftEnd)"
Set rs = db.OpenRecordset(sSQL, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ShiftFind = "<No Shift>"
Else
ShiftFind = rs!ShiftName
End If
rs.Close
Else
ShiftFind = "*** Invalid time ***"
End If
End Function

You could use the WHERE clause you have constructed in a DLookup if you
prefer, but opening a recordset this way is faster.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I wanted to find out which factory shift, a particular time (Now())
falls
into


TblShift has

ShiftName,-ShiftStart(time), ShiftEnd(Time)

Shift1- 09:00:00 -14:00:00

Shift2 - 14:00:01 - 16:00:00

Shift3 - 18:00:01 - 04:00:00



So if Now() = 14:30, I want the result to be "Shift2"

because 14:30 is between 14:00: and 16:00



If Now() = 08:00, I want it to read "No Shift"



Dlookup between ShiftStart and ShiftEnd worked until Midnight struck, then
I
got a "No Shift" because Now() at 01:00 was no longer greater than or
equal
to ShiftStart and less than or equal To ShiftEnd - it was less than
both
these values.



I couldn't figure out an IIF to sift this out.



To make this work, I've added a dummy date to the shift times
25/12/2008
with the Over-midnight shiftEnd being 26/12/08

I've formatted them as General Date

This function does the trick but was there an easier way without using
Recordset (which doesn't always translate easily over different
versions
of
Access)?



(I've left the dtFind variant there so that the function can be used
say
to
look up a worker's shift by using the time he started work)



Function ShiftFind(ByVal dtFind As Variant) As String

Dim dtNow As Date

Dim Dummydate As Date

Dim MyFindField As String

Dim MyStartField As String

Dim MyEndField As String

Dim MyTable As String

Dim MyStart As Date

Dim MyEnd As Date

Dim MyFind As String

Dim rs As Recordset

Dim a As Integer



If Not IsNull(dtFind) Then

'change these names to match your Table

MyStartField = "ShiftStart"

MyEndField = "ShiftEnd"

MyFindField = "ShiftName"

MyTable = "QryShiftTest"

'Query is sorted by StartTime



Dummydate = #12/25/2008#

dtNow = Dummydate + TimeValue(dtFind)



Set rs = CurrentDb.OpenRecordset(MyTable)



rs.MoveFirst





Do Until rs.EOF

MyStart = rs(MyStartField)

MyEnd = rs(MyEndField)

MyFind = rs(MyFindField)



If TimeValue(MyEnd) < TimeValue(MyStart) And dtNow < MyStart
Then



'ie shift spans midnight and dtNow is after midnight

'add 1 day to dtNow



dtNow = dtNow + 1



Else

dtNow = dtNow

End If



If dtNow >= MyStart And dtNow <= MyEnd Then

ShiftFind = MyFind

GoTo FoundIt

'break out of loop

Else

rs.MoveNext

'it wasn't the right time so move to next record

End If





Loop

ShiftFind = "No shift"

End If

FoundIt:

rs.Close

Set rs = Nothing





End Function



Evi

 

[Evi]

Hi Graham, thanks for the explanation. I can see how this could be useful.

Your code does work - it's great! I realised it didn't work before because I
added a date to the ShiftTimes when i was trying my version. As soon as I
stripped away the date all worked.

Evi

Hi Evi

What does dbOpenForwardOnly do? I've never seen that before and

It opens a forward-only snapshot. It is much faster and more efficient to
open a recordset that is read-only and not updated by other users' changes
(a snapshot) which is only read in one direction, with no ability to search
or move backwards (forward-only).

Is there a reason for using
set currentdb?

This is not strictly necessary in this case. You can actually just say:
Set rs = CurrentDb.OpenRecordset(...)
However, there are many more cases when referring to CurrentDb directly does
not work as expected, and it's therefore good practice (IMHO) always to
assign it to a local variable.

It's finding everything as No Shift Graham and I really don't see why. It
looks perfectly logical to me. The field names are fine.

That is strange. I have just created a test table with exactly your data
and it appears to work fine. Does it return *everything* as <No Shift> or
just the boundary values (on the shift change times)?

You could try copying the value of sSQL and pasting it into a the SQL window
of a new query. That might indicate where the problem lies when you try to
run it.

The reason I wanted to avoid the Recordset code is below in Opal's
message.
The function I have does work on my PC (Acc2000) but Opal (Acc2003) is
getting an error message which may be to do with references.

As I said, you can build just the WHERE string (omit the "Select * from
TblShift where " part) and use that in a DLookup. However, opening a
recordset is faster.

Every version of Access has DAO. The problem is that for one or two
versions (2000 and 2002 I think) the reference was not included by default
in new databases.

Sorry, Evi. I didn't realise till later that Opal, not you, was the
original poster. I hope he/she has seen my post and tried the code.
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

It's finding everything as No Shift Graham and I really don't see why. It
looks perfectly logical to me. The field names are fine.

What does dbOpenForwardOnly do? I've never seen that before and

Is there a reason for using
set currentdb?
I notice most people do it but as CurrentDb is often only used once in the
code, why do so many people bother to set it as a variable?

The reason I wanted to avoid the Recordset code is below in Opal's
message.
The function I have does work on my PC (Acc2000) but Opal (Acc2003) is
getting an error message which may be to do with references.
Evi

Hi Evi

If the shift starts and ends on the same day (ShiftStart<ShiftEnd) then the
time will need to be between ShiftStart and ShiftEnd.

If ShiftEnd is earlier than ShiftStart, then the shift crosses midnight and
the logic needs to be reversed. The given time must be greater than
ShiftEnd OR earlier than ShiftStart.

Putting all that together, you can use a function like this:

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
If IsDate(dtFind) Then
sTime = Format(dtFind, "\#hh:nn:ss\#")
Set db = CurrentDb
sSQL = "Select * from TblShift where (" _
& sTime & ">=ShiftStart And " _
& sTime & "<ShiftEnd And ShiftStart<ShiftEnd) OR ((" _
& sTime & "<ShiftEnd Or " _
& sTime & ">=ShiftStart) And ShiftStart>ShiftEnd)"
Set rs = db.OpenRecordset(sSQL, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ShiftFind = "<No Shift>"
Else
ShiftFind = rs!ShiftName
End If
rs.Close
Else
ShiftFind = "*** Invalid time ***"
End If
End Function

You could use the WHERE clause you have constructed in a DLookup if you
prefer, but opening a recordset this way is faster.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I wanted to find out which factory shift, a particular time (Now())
falls
into


TblShift has

ShiftName,-ShiftStart(time), ShiftEnd(Time)

Shift1- 09:00:00 -14:00:00

Shift2 - 14:00:01 - 16:00:00

Shift3 - 18:00:01 - 04:00:00



So if Now() = 14:30, I want the result to be "Shift2"

because 14:30 is between 14:00: and 16:00



If Now() = 08:00, I want it to read "No Shift"



Dlookup between ShiftStart and ShiftEnd worked until Midnight struck, then
I
got a "No Shift" because Now() at 01:00 was no longer greater than or
equal
to ShiftStart and less than or equal To ShiftEnd - it was less than
both
these values.



I couldn't figure out an IIF to sift this out.



To make this work, I've added a dummy date to the shift times
25/12/2008
with the Over-midnight shiftEnd being 26/12/08

I've formatted them as General Date

This function does the trick but was there an easier way without using
Recordset (which doesn't always translate easily over different
versions
of
Access)?



(I've left the dtFind variant there so that the function can be used
say
to
look up a worker's shift by using the time he started work)



Function ShiftFind(ByVal dtFind As Variant) As String

Dim dtNow As Date

Dim Dummydate As Date

Dim MyFindField As String

Dim MyStartField As String

Dim MyEndField As String

Dim MyTable As String

Dim MyStart As Date

Dim MyEnd As Date

Dim MyFind As String

Dim rs As Recordset

Dim a As Integer



If Not IsNull(dtFind) Then

'change these names to match your Table

MyStartField = "ShiftStart"

MyEndField = "ShiftEnd"

MyFindField = "ShiftName"

MyTable = "QryShiftTest"

'Query is sorted by StartTime



Dummydate = #12/25/2008#

dtNow = Dummydate + TimeValue(dtFind)



Set rs = CurrentDb.OpenRecordset(MyTable)



rs.MoveFirst





Do Until rs.EOF

MyStart = rs(MyStartField)

MyEnd = rs(MyEndField)

MyFind = rs(MyFindField)



If TimeValue(MyEnd) < TimeValue(MyStart) And dtNow < MyStart
Then



'ie shift spans midnight and dtNow is after midnight

'add 1 day to dtNow



dtNow = dtNow + 1



Else

dtNow = dtNow

End If



If dtNow >= MyStart And dtNow <= MyEnd Then

ShiftFind = MyFind

GoTo FoundIt

'break out of loop

Else

rs.MoveNext

'it wasn't the right time so move to next record

End If





Loop

ShiftFind = "No shift"

End If

FoundIt:

rs.Close

Set rs = Nothing





End Function



Evi

 

[Steve Sanford]

Hi Evi,

One thing I noticed - you are missing 2 hours between the end of Shift 2 and
the start of Shift 3. Shouldn't the end of Shift 2 be "18:00" (6 PM), not
"16:00" (4PM)??


Just for fun, I came up with a function that gets the shift using the IF()
function.
If you have contiguous times, you don't need the end times... so I didn't
use them


'---------------------------------
Function GetShift(ByVal dtFind As Variant) As String
'
' determines which shift a time falls in
'
Dim TheTime As Date

Dim Shift1 As Date
Dim Shift2 As Date
Dim Shift3 As Date
Dim NoShift As Date

' shift start times
Shift1 = #9:00:00 AM# ' to #2:00:00 PM#
Shift2 = #2:00:01 PM# ' to #6:00:00 PM#
Shift3 = #6:00:01 PM# ' to #4:00:00 AM#
NoShift = #4:00:01 AM# ' to #8:00:59 AM#

' set default return value
GetShift = "*** Invalid time ***"

If IsDate(dtFind) Then

' the date doesn't matter
' convert parameter to time format
TheTime = TimeValue(dtFind)

' now determine which shift
If TheTime >= NoShift And TheTime < Shift1 Then
GetShift = "<NoShift>"
ElseIf TheTime >= Shift1 And TheTime < Shift2 Then
GetShift = "Shift1"
ElseIf TheTime >= Shift2 And TheTime < Shift3 Then
GetShift = "Shift2"
Else
GetShift = "Shift3"
End If
End If

End Function
'---------------------------------

HTH

 

[Evi]

Hi Steve, ace function! And if the times are consecutive (which in real
life, they would usually be), it doesn't even require a finishing time in
the table.
The gap was deliberate. I wanted to see if there was something that would
also work if the shifts weren't completely consecutive.
Evi

 

[Opal]

Hi Steve, ace function! And if the times are consecutive (which in real
life, they would usually be), it doesn't even require a finishing time in
the table.
The gap was deliberate. I wanted to see if there was something that would
also work if the shifts weren't completely consecutive.
Evi
"Steve Sanford" <limbim53 at yahoo dot com> wrote in message














- Show quoted text -

Wow! Thank you guys, you all have been so helpful. I tried Graham's
code while I was at work but couldn't sign into google at work (damn
IT group)
to let you know that it worked like a charm. Now this is really
coming
together.

Does anyone know anything about emailing Reports from Access?
I will be creating another post soon ;-)

 

[Graham Mandeno]

I'm glad it did the trick for you, Opal.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Steve, ace function! And if the times are consecutive (which in real
life, they would usually be), it doesn't even require a finishing time in
the table.
The gap was deliberate. I wanted to see if there was something that would
also work if the shifts weren't completely consecutive.
Evi
"Steve Sanford" <limbim53 at yahoo dot com> wrote in
message














- Show quoted text -

Wow! Thank you guys, you all have been so helpful. I tried Graham's
code while I was at work but couldn't sign into google at work (damn
IT group)
to let you know that it worked like a charm. Now this is really
coming
together.

Does anyone know anything about emailing Reports from Access?
I will be creating another post soon ;-)

 

[Opal]

I'm glad it did the trick for you, Opal.
--
Good Luck  

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Steve, ace function! And if the times are consecutive (which in real
life, they would usually be), it doesn't even require a finishing time in
the table.
The gap was deliberate. I wanted to see if there was something that would
also work if the shifts weren't completely consecutive.
Evi
"Steve Sanford" <limbim53 at yahoo dot com> wrote in
messagenews:[email protected]...

- Show quoted text -

Wow!  Thank you guys, you all have been so helpful.  I tried Graham's
code while I was at work but couldn't sign into google at work (damn
IT group)
to let you know that it worked like a charm.  Now this is really
coming
together.

Does anyone know anything about emailing Reports from Access?
I will be creating another post soon ;-)- Hide quoted text -

- Show quoted text -

Graham,

Can you assist with one more "variable"?
Monday to Thursday the 2nd shift start and end time are
consistent, but on Fridays the 2nd shift starts an hour earlier
and ends 2 hours earlier. I need to account for the "day of
the week" in the code to account for daily shift changes.

I then need to modify this same code to allow me to show
the "hour" in the shift, i.e. the purpose of the database is
to track on an hourly basis repairs that are done and report
out on them and with the shift daily schedule changes, the
hourly schedule changes. I have both schedules in tables
but, again, need to account for the day of the week. I can
I do this properly with your code?

Thank you so much!

 

[Graham Mandeno]

Hi Opal

First, you will need to add a field to your TblShift table for the day of
the week. It may be tempting to add a "Friday or NOT Friday" field, but
this would be bad design, as you would be in trouble if a changed shift was
introduced on Wednesday. Besides, what happens on Saturday and Sunday?
Presumably you want times on those days to return "No Shift".

So, add a numeric byte field "ShiftDay". I suggest you use the built-in
Windows/VB code values for the day: Sunday=1, Monday=2, etc.

ShiftDay would be the day of the shift (obviously!) if the start and end
were on the same day, and would be the day the shift starts if
ShiftStart>ShiftEnd.

OK, so here is the logic. The matching record (if one exists) is the one
where:

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

OR

(ShiftStart>ShiftEnd)
AND
(
(the time is >= ShiftStart) AND (ShiftDay matches the given date)
OR
(the time is < ShiftEnd) AND (the given date is one day after ShiftDay)
)

Does all that make sense? (I hope so, because it's after midnight here!
<g>)

OK, so I'll leave it up to you to construct a SQL statement to represent all
of this. Here are some hints:

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

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

See how you get on designing a SQL statement and constructing it in VBA from
your variables (dtFind and sTime).

If you can't get it working, then post back here with your code that you
have tried.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham,

Can you assist with one more "variable"?
Monday to Thursday the 2nd shift start and end time are
consistent, but on Fridays the 2nd shift starts an hour earlier
and ends 2 hours earlier. I need to account for the "day of
the week" in the code to account for daily shift changes.

I then need to modify this same code to allow me to show
the "hour" in the shift, i.e. the purpose of the database is
to track on an hourly basis repairs that are done and report
out on them and with the shift daily schedule changes, the
hourly schedule changes. I have both schedules in tables
but, again, need to account for the day of the week. I can
I do this properly with your code?

Thank you so much!

 

[Opal]

Hi Opal

First, you will need to add a field to your TblShift table for the day of
the week.  It may be tempting to add a "Friday or NOT Friday" field, but
this would be bad design, as you would be in trouble if a changed shift was
introduced on Wednesday.  Besides, what happens on Saturday and Sunday?
Presumably you want times on those days to return "No Shift".

So, add a numeric byte field "ShiftDay".  I suggest you use the built-in
Windows/VB code values for the day: Sunday=1, Monday=2, etc.

ShiftDay would be the day of the shift (obviously!) if the start and end
were on the same day, and would be the day the shift starts if
ShiftStart>ShiftEnd.

OK, so here is the logic.  The matching record (if one exists) is the one
where:

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

   OR

(ShiftStart>ShiftEnd)
AND
(
    (the time is >= ShiftStart) AND (ShiftDay matches the given date)
    OR
    (the time is < ShiftEnd) AND (the given date is one day after ShiftDay)
)

Does all that make sense?  (I hope so, because it's after midnight here!
<g>)

OK, so I'll leave it up to you to construct a SQL statement to represent all
of this.  Here are some hints:

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

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

See how you get on designing a SQL statement and constructing it in VBA from
your variables (dtFind and sTime).

If you can't get it working, then post back here with your code that you
have tried.
--
Good Luck  

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,

Can you assist with one more "variable"?
Monday to Thursday the 2nd shift start and end time are
consistent, but on Fridays the 2nd shift starts an hour earlier
and ends 2 hours earlier.  I need to account for the "day of
the week" in the code to account for daily shift changes.

I then need to modify this same code to allow me to show
the "hour" in the shift, i.e. the purpose of the database is
to track on an hourly basis repairs that are done and report
out on them and with the shift daily schedule changes, the
hourly schedule changes.  I have both schedules in tables
but, again, need to account for the day of the week.  I can
I do this properly with your code?

Thank you so much!

Graham,

I already have a "DayofWeek" text box on my form with the following
code in the On Timer event:

txtDayofWeek = DatePart("w", txtTodaysDate)

couldn't I just use something along the lines of:

ShiftDay=Forms![txtDayofWeek] in the code?

Or am I way off the mark here?

 

[Graham Mandeno]

Hi Opal

You have a Timer event on your form? Which form? What does it do?

Furthermore, what is in txtTodaysDate? If it is, as it suggests, the
current date, then you can get that in code from the built-in Date function
and you don't need any textbox or Timer event.

You will still need to modify the table, adding the ShiftDay field as I
suggested, because otherwise the code that looks up the shift will have no
way to distinguish between Shift 2 on a Friday and Shift 2 on any other day.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Opal

First, you will need to add a field to your TblShift table for the day of
the week. It may be tempting to add a "Friday or NOT Friday" field, but
this would be bad design, as you would be in trouble if a changed shift
was
introduced on Wednesday. Besides, what happens on Saturday and Sunday?
Presumably you want times on those days to return "No Shift".

So, add a numeric byte field "ShiftDay". I suggest you use the built-in
Windows/VB code values for the day: Sunday=1, Monday=2, etc.

ShiftDay would be the day of the shift (obviously!) if the start and end
were on the same day, and would be the day the shift starts if
ShiftStart>ShiftEnd.

OK, so here is the logic. The matching record (if one exists) is the one
where:

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

OR

(ShiftStart>ShiftEnd)
AND
(
(the time is >= ShiftStart) AND (ShiftDay matches the given date)
OR
(the time is < ShiftEnd) AND (the given date is one day after ShiftDay)
)

Does all that make sense? (I hope so, because it's after midnight here!
<g>)

OK, so I'll leave it up to you to construct a SQL statement to represent
all
of this. Here are some hints:

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

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

See how you get on designing a SQL statement and constructing it in VBA
from
your variables (dtFind and sTime).

If you can't get it working, then post back here with your code that you
have tried.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,

Can you assist with one more "variable"?
Monday to Thursday the 2nd shift start and end time are
consistent, but on Fridays the 2nd shift starts an hour earlier
and ends 2 hours earlier. I need to account for the "day of
the week" in the code to account for daily shift changes.

I then need to modify this same code to allow me to show
the "hour" in the shift, i.e. the purpose of the database is
to track on an hourly basis repairs that are done and report
out on them and with the shift daily schedule changes, the
hourly schedule changes. I have both schedules in tables
but, again, need to account for the day of the week. I can
I do this properly with your code?

Thank you so much!

Graham,

I already have a "DayofWeek" text box on my form with the following
code in the On Timer event:

txtDayofWeek = DatePart("w", txtTodaysDate)

couldn't I just use something along the lines of:

ShiftDay=Forms![txtDayofWeek] in the code?

Or am I way off the mark here?

 

[Opal]

Graham,

A major part of Opal's form is a super-duper work shift
clock using a bunch of unbound controls.  Your function and
the table is part of that as it is needed to determine which
shift is currently working.

Thank you Graham and Marsh....

Yes, the main form where the shift find function you provided above is
also
a big part of. I have added the Shift day field to the table as
suggested but I am
unclear as to how to add it to the function so that it will look up
the correct
shift on the correct day....that is why I asked about the ShiftDay =
the value
in the text box on the form.....

 

[Graham Mandeno]

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() and an
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.

Check back if you need more hints.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

A major part of Opal's form is a super-duper work shift
clock using a bunch of unbound controls. Your function and
the table is part of that as it is needed to determine which
shift is currently working.

Thank you Graham and Marsh....

Yes, the main form where the shift find function you provided above is
also
a big part of. I have added the Shift day field to the table as
suggested but I am
unclear as to how to add it to the function so that it will look up
the correct
shift on the correct day....that is why I asked about the ShiftDay =
the value
in the text box on the form.....

 

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