Interval Time

i Need to make time interval, but I have problem when over midnight, can you tell me how to do it.

example

1. between 07.00 - 17.00 is belong to Shift A
2. between 17.00 - 23.00 is belong to Shift B
3. between 23:00 - 07:00 is belong to Shift C

I Make like this

Option Explicit
Dim rss As Recordset
Dim conn As Connection

Private Sub Form_Load()

Dim wkt As Date
Dim wkt1 As Date

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "/absdata/data.mdb;Persist Security Info=False"
conn.Open

Set rss = New ADODB.Recordset
rss.Open "select * from shift", conn, adOpenKeyset, adLockOptimistic
rss.MoveFirstrss.MoveFirst
Do Until rss.EOF
wkt = Date & " " & CDate(TimeValue(rss.Fields("Start")) - TimeValue("1:00"))
If rss.Fields("ND") = 1 Then
wkt1 = Date + 1 & " " & rss.Fields("finish")
Else
wkt1 = Date & " " & rss.Fields("finish")
End If
If Now() >= wkt And Now() < wkt1 Then
shift.Text = rss.Fields("ShiftName")
End If
rss.MoveNext
Loop
end sub



But the problem when I open form on midnight, doesn't work

thanks in advance

eka

Can you please wrap your code in [highlight="vb"][/highlight] tags! It makes the code easier too read.

Here's a way of determining the Shift from the 'Clock out' time.

Code:

---

Private Function FindShift(daEndTime As Date) As String
'
' Function takes Clock out Time and returns the Shift Letter
' (Assumes 24 hour time format)
'
Dim lngSec As Long
Dim lngShiftAs As Long
Dim lngShiftAe As Long
Dim lngShiftBs As Long
Dim lngShiftBe As Long
lngShiftAs = 7& * 3600&                        'Shift A Start in Seconds
lngShiftAe = 17& * 3600&                        'Shift A End in Seconds
lngShiftBs = lngShiftAe                        'Shift B Start in Seconds
lngShiftBe = 23& * 3600&                        'Shift B End in Seconds
lngSec = CLng(Format(daEndTime, "hh")) * 60&
lngSec = (lngSec + CLng(Format(daEndTime, "mm"))) * 60&
lngSec = lngSec + CLng(Format(daEndTime, "ss")) 'Time of Clock Out in Seconds
If lngSec > lngShiftAs And lngSec < lngShiftAe Then
    FindShift = "A"
Else
    If lngSec > lngShiftBs And lngSec < lngShiftBe Then
        FindShift = "B"
    Else
        FindShift = "C"
    End If
End If
End Function

---

dear Doogle,

thank for your code, it's work but the code is static, how can i make it dynamic. cause the shift can be on access database, the shift maybe more then 3 interval, like the above sample. it is not fix time as above. How can i make it?

thanks a lot

OK, Let's just extend the theory.

Code:

---

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

---

Dear Doogle,
Thanks For your code, it's work, it's nice trick to make accending order on shiftstart, this code will get the last records if over mid night, but if we not make it accending, this will get the last one. So Now I Understand it.

Thanks a lot

The code I posted is not particularly efficient and can be tidied up, but I'm glad you found it useful to understand the technique.

Good luck with the rest of the Project.