Interval Time

[Doogle]

OK, Let's just extend the theory.

Option Explicit
'
' Given a 'Clock-Out time will determine which Shift was worked
'
' Assumes a Table named Shifts in an Access Database
' located at App.Path /Payroll.mdb
' Shifts has the following Columns defined
'
' ShiftName     Type:Text
' ShiftStart    Type:Date / Time
' ShiftEnd      Type:Date / Time
'
' Data, for example might be
'
'   ShiftName       ShiftStart  ShiftEnd
'   Shift 07 - 12   07:00:00    12:00:00
'   Shift 17 - 23   17:00:00    23:00:00
'   Shift 23 - 00   23:00:00    00:00:00
'   Shift 12 - 17   12:00:00    17:00:00
'   Shift 00 - 07   00:00:00    07:00:00
'
' Clock-Out time is in Text1.Text in the format hh:mm:ss
'
' Constraints:
' 1. The Full 24 hours must be covered
' 2. There can be no Gaps between shifts
'

Private db As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub cmdShift_Click()
Dim strShift As String
Dim strSQL As String
Dim lngStart As Long
Dim lngEnd As Long
Dim lngClockOut As Long
Dim boDone As Boolean
strSQL = "SELECT * FROM Shifts ORDER BY ShiftStart"
rs.Open strSQL, db, adOpenStatic, adLockOptimistic
If rs.RecordCount > 0 Then
    Do
        lngStart = CLng(Format(rs![ShiftStart], "hh")) * 60
        lngStart = (lngStart + (CLng(Format(rs![ShiftStart], "mm")))) * 60
        lngStart = lngStart + CLng(Format(rs![ShiftStart], "ss"))
        lngEnd = CLng(Format(rs![ShiftEnd], "hh")) * 60
        lngEnd = (lngEnd + (CLng(Format(rs![ShiftEnd], "mm")))) * 60
        lngEnd = lngEnd + CLng(Format(rs![ShiftEnd], "ss"))
        lngClockOut = CLng(Format(Text1.Text, "hh")) * 60
        lngClockOut = (lngClockOut + CLng(Format(Text1.Text, "mm"))) * 60
        lngClockOut = lngClockOut + CLng(Format(Text1.Text, "ss"))
        If lngClockOut >= lngStart And lngClockOut <= lngEnd Then
            strShift = rs![ShiftName]
            boDone = True
        Else
            strShift = rs![ShiftName]
            rs.MoveNext
        End If
    Loop Until boDone = True Or rs.EOF = True
    Debug.Print "Time: "; Text1.Text; " is in Shift "; strShift
Else
    Debug.Print "No Shift Patterns have been set-up in the Database"
End If
rs.Close
Text1.Text = ""
End Sub

Private Sub Form_Load()
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Payroll.mdb;"
Set rs = New ADODB.Recordset
Text1.Text = ""
End Sub