The comments from Andy and Duane are on the money. The fundamental issue that you will need to deal with is missing or incorrectly entered data. People do forget, or for whatever reason are prevented from, faithfully clocking in and clocking out. If your user interface allows a user to choose "I'm clocking in" or "I'm clocking out" thus setting your In/Out field then you need to deal with successive "In" entries with no "Out" between them or conversely, two "Outs" with no intervening "In". That is actually a much bigger problem than just computing elapsed times.
A few other potential issues.
- Is this system intended to form the basis for people's pay? If it is then you need a full audit trail. People get real touchy about their paychecks.
- Do you have people starting work on one day and finishing the next? If so then you will need to handle allocating the time worked to different days. That may be important because such a day roll over on the last day of a month could mean that some of the time appears on one paycheck and some on the next. Similar comments apply to working holidays if different rates apply than those for a regular work day.
- Do you pay overtime? In some jurisdictions overtime is a legal requirement beyond a certain number of hours per day. Do you allow an employee who is supposed to start work at 9:00 AM to clock in at 6:00 AM, thus awarding himself three hours of overtime?
- Some companies compute pay in (for example) 15 minute increments. That means that, before computing time worked, you need to round the raw clock times to the appropriate 15 minute break points before computing the time worked.
- Is this system tied to a scheduling function? If so, is an employee allowed to clock in when they are not scheduled to work? If they cannot, is there some management over-ride so that the boss can call Joe on his day off to come in and fix some problem?
Given all that, here's some code that assumes:
- Employees never fail to clock-in and clock-out.
- In/Out pairs always occur on the same day.
- Elapsed time is based on the raw times.
- There are no restrictions on when an employee can clock in or out.
Code:
Public Sub ComputeTimes(FromDate As Date, ToDate As Date)
Dim db As DAO.Database
Dim re As DAO.Recordset ' List of employees
Dim rt As DAO.Recordset ' Clock Times for an Employee
Dim SQL As String
Dim n As Integer
Dim ElapsedTime As Long
Dim ThisDate As Date
Dim InTime As Date
Dim OutTime As Date
On Error GoTo UnPleasentness
Set db = CurrentDb
' Make sure that the input parameters do not have a time value
FromDate = CDate(Int(FromDate))
ToDate = CDate(Int(ToDate))
' Get a list of employees that have at least one time
' between the specified dates.
Set re = db.OpenRecordset( _
"SELECT DISTINCT EmployeeNum From TheTable " & _
"WHERE CDate(Int(ClockDT)) Between #" & FromDate & "# AND #" & ToDate & "#;", _
dbOpenSnapshot)
' Start the loop to process the employees
Do Until re.EOF
' Process each date in the range FromDate ---> ToDate
ThisDate = FromDate
Do Until ThisDate > ToDate
SQL = "SELECT ClockDT FROM TheTable " & _
"WHERE EmployeeNum = " & re![EmployeeNum] & _
" AND CDate(Int(ClockDT)) = #" & ThisDate & "# " & _
"ORDER BY ClockDT "
Set rt = db.OpenRecordset(SQL, dbOpenDynaset)
If rt.EOF And rt.BOF Then
' The employee has no records on ThisDate
Else
n = 0
ElapsedTime = 0
Do Until rt.EOF
n = n + 1
If n Mod 2 = 1 Then
' Odd Number ---> Clock In
InTime = rt![ClockDT]
Else
' Even Number ---> Clock Out
OutTime = rt![ClockDT]
ElapsedTime = ElapsedTime + DateDiff("n", InTime, OutTime)
End If
rt.MoveNext
Loop
If n Mod 2 = 1 Then
' Raise an error ... We found an Odd-Number of values
Else
' Done with that employee on that Date. Save the results
SQL = "INSERT INTO tblTimes (EmployeeNum, WorkDate, WorkMinutes) " & _
"VALUES (" & re![EmployeeNum] & ", #" & ThisDate & _
"#, " & ElapsedTime & ")"
db.Execute SQL, dbFailOnError
End If
End If
' On To the next Day
ThisDate = ThisDate + 1
Loop
' And do another employee
re.MoveNext
Loop
Normal_Exit:
Set re = Nothing
Set rt = Nothing
Set db = Nothing
Exit Sub
UnPleasentness:
MsgBox Err.Number & " - " & Err.Description
Resume Normal_Exit
End Sub
