Check for Duplicate Shifts

[Diarmuid]

Hi
I have a client who enters employee Shifts. The 3 fields that mark a
shift as duplicate are if the TimeDate is the same, and the TimeStart and
TimeStop overlap. The code I'm currently using is also below, Seems easy,
but I've come across some problems. Best way is with some examples of the
problems. Assume all these are for the same date


CASE 1
Shift 1: 10:00 to 10:30
Shift 2: 09:00 to 11:00

Shift 2 won't come up as a duplicate, as the times are outside Shift 1.

CASE 2:
Shift 1: 22:00 to 08:00
Shift 2: 22:00 to 08:00

Here the problem is that Timestop is actually the next day.

I'm looking for an efficient way of checking, rather that just loads if
statements. Any ideas?
Thanks
Diarmuid




'***********************************************
stSQL = "SELECT tabTimeDetails.Det_Date, tabTimeDetails.DetailID,
tabTimeDetails.TimeStart, tabTimeDetails.TimeStop, " & _
"tabTimesheet.Employee_Number FROM tabEmployees INNER JOIN
(tabTimesheet INNER JOIN " & _
"tabTimeDetails ON tabTimesheet.ID =
tabTimeDetails.TimesheetID) ON " & _
"tabEmployees.Employee_Number = tabTimesheet.Employee_Number
WHERE " & _
"(((tabTimeDetails.Det_Date)= #" & stDate & "#) AND
((tabTimesheet.Employee_Number)= '" & _
stEmployee_Number & "' AND DetailID <> " & lDetailID & "))"

Set rsSet = dbs.OpenRecordset(stSQL, dbOpenForwardOnly)
With rsSet
While (Not rsSet.EOF And bOverLap = False)
If ((tStartTime >= !TimeStart) And (tStartTime < !TimeStop))
Then
' Check Start Time
bOverLap = True
MsgBox lDetailID & " Start Time overlaps with Shift " &
!DetailID, vbExclamation
Else
' Check Stop Time
If ((tStopTime > !TimeStart) And (tStopTime <=
!TimeStop)) Then
bOverLap = True
MsgBox lDetailID & " Stop Time overlaps with Shift "
& !DetailID, vbExclamation
End If
End If

rsSet.MoveNext
Wend
.Close

 

[Mingqing Cheng [MSFT]]

Hi Diarmuid,

I understood you would like to eliminate duplicate shift of your keys.
However, I am a little bit confused with your descriptions What's the
result of CASE 2? What's your exceptions? Will Shift 2 in CASE 2 be
recognized as a duplicated one?

Based on my understanding, CASE 2 will still be false for bOverLap as
TimeDate is not the same? If so, you could make a standard datetime
variable as "00:00" and compare TimeSrart and TimeStop with the standard
datetime variable.

If you need my detailed scripts for you, it is highly appreciated if you
could provide me a sample mdb files, which, I believe, will be better for
me to troubleshooting and make sample codes. (Kindly Reminder: Please
ensure all necessary tables and mdw files are included in your compressed
package)

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Diarmuid]

I found the best way to be to convert to long, add 24 if its the next day,
and then compare both ways.Code is below. It may not be neat, but it works!
Diarmuid


Set rsSet = dbs.OpenRecordset(stSQL, dbOpenForwardOnly)
With rsSet


lStartTime = (Hour(tStartTime) * 100) + Minute(tStartTime)
lStopTime = (Hour(tStopTime) * 100) + Minute(tStopTime)
If lStartTime > lStopTime Then
lStopTime = lStopTime + 2400
End If

While (Not rsSet.EOF And bOverLap = False)

lShiftStart = (Hour(!TimeStart) * 100) + Minute(!TimeStart)
lShiftStop = (Hour(!TimeStop) * 100) + Minute(!TimeStop)
If lShiftStart > lShiftStop Then
lShiftStop = lShiftStop + 2400
End If

If ((lShiftStart = lStartTime) And (lShiftStop = lStopTime))
Then
bOverLap = True
MsgBox lDetailID & " is a duplicate of " & !DetailID,
vbExclamation
Else
If bfunIsDuplicate(lStartTime, lStopTime, lShiftStart,
lShiftStop) Then
bOverLap = True
MsgBox lDetailID & " overlaps with Shift " &
!DetailID, vbExclamation
Else
' Now check the other way round
If bfunIsDuplicate(lShiftStart, lShiftStop,
lStartTime, lStopTime) Then
bOverLap = True
MsgBox lDetailID & " overlaps with Shift " &
!DetailID, vbExclamation
End If
End If

End If

rsSet.MoveNext
Wend
.Close
End With
End If


dbs.Close

End Function


Private Function bfunIsDuplicate(lStartTime As Long, lStopTime As Long,
lShiftStart As Long, lShiftStop As Long) As Boolean
' Check if Numbers Duplicate


If ((lStartTime >= lShiftStart) And (lStartTime < lShiftStop)) Then
' Check Start Time
bfunIsDuplicate = True
Else
' Check Stop Time
If ((lStopTime > lShiftStart) And (lStopTime <= lShiftStop)) Then
bfunIsDuplicate = True
End If
End If

End Function

Hi Diarmuid,

I understood you would like to eliminate duplicate shift of your keys.
However, I am a little bit confused with your descriptions What's the
result of CASE 2? What's your exceptions? Will Shift 2 in CASE 2 be
recognized as a duplicated one?

Based on my understanding, CASE 2 will still be false for bOverLap as
TimeDate is not the same? If so, you could make a standard datetime
variable as "00:00" and compare TimeSrart and TimeStop with the standard
datetime variable.

If you need my detailed scripts for you, it is highly appreciated if you
could provide me a sample mdb files, which, I believe, will be better for
me to troubleshooting and make sample codes. (Kindly Reminder: Please
ensure all necessary tables and mdw files are included in your compressed
package)

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Mingqing Cheng [MSFT]]

Hi Diarmuid,

It's great to hear that you have resolved it by adding 24

Thanks for sharing it with newsgroup communities.


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support