First, an assumption that your date and time fields are really Text fields.
Next, you need to change the names of the Date and Time fields, since
both are reserved words which will create confusion for the system.
Try Datex and Timex.
In order to take advantage of Access' date & time related functions,
the text representations need to be converted to actual date/time.
Recommend you check out this article which will provide insight as to
how dates / times are stored by Access.
As you'll see, a Date/Time data type is stored as a double-precision,
floating-point number (up to 15 decimal places).
- The integer portion of the double-precision number representing the date
- The decimal portion representing the time.
Assuming your table is named tblEmpTimesOld and the fields
are Datex and Timex, here's a query you can run which will show your dates /
times converted to a date/time data type, and also how it's stored by Access.
Code:
SELECT tblEmpTimesOld.EmpID, tblEmpTimesOld.Datex, tblEmpTimesOld.Timex, DateValue(Mid([datex],3,2) & "/" & Left([datex],2) & "/" & Right([datex],2))+TimeValue(Left([timex],2) & ":" & Right([timex],2)) AS realDT, CDbl([realDT]) AS stored
FROM tblEmpTimesOld
ORDER BY tblEmpTimesOld.EmpID, DateValue(Mid([datex],3,2) & "/" & Left([datex],2) & "/" & Right([datex],2))+TimeValue(Left([timex],2) & ":" & Right([timex],2));
To get your start and end date/times (can see no point in
separating the date and the time in individual fields) into one
record, create a new table (tblEmpTimesNew) with fields:
EmpID: Text
StartTime: date/time (format as general date)
EndTime: date/time (format as general date)
PROVIDED that your records are matched pairs (a start
date/time record and a corresponding end date/time record),
the following code, placed in a new module should fill your new
table. Call it from the debug window with: ? TimeConv()
Code:
Function TimeConv()
Dim db As DATABASE
Dim rs1 As Recordset, rs2 As Recordset
Dim idHold As String, strSQL As String
Dim i As Integer, n As Integer
Set db = CurrentDb
strSQL = "SELECT tblEmpTimesOld.EmpID, tblEmpTimesOld.Datex, tblEmpTimesOld.Timex, DateValue(Mid([datex],3,2) & '/' & Left([datex],2) & '/' & Right([datex],2))+TimeValue(Left([timex],2) & ':' & Right([timex],2)) AS realDT" _
& " FROM tblEmpTimesOld" _
& " ORDER BY tblEmpTimesOld.EmpID, DateValue(Mid([datex],3,2) & '/' & Left([datex],2) & '/' & Right([datex],2))+TimeValue(Left([timex],2) & ':' & Right([timex],2));"
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset("tblEmpTimesNew")
'ensure that rs1 contains records
rs1.MoveLast
i = rs1.RecordCount
rs1.MoveFirst
If i > 0 Then
Do While Not rs1.EOF
idHold = rs1!EmpID
Do While rs1!EmpID = idHold
With rs2
.AddNew
!EmpID = idHold
For n = 1 To 2
If n = 1 Then
!starttime = rs1!realDT
Else
!endtime = rs1!realDT
End If
rs1.MoveNext
If rs1.EOF Then Exit For
Next n
.Update
End With
If rs1.EOF Then Exit Do
Loop
Loop
End If
rs1.Close
rs2.Close
db.Close
Set db = Nothing
End Function
Assuming DATE, TIME are date/time fields in table tblPunchCard, try these two queries (type/paste in the SQL View of each new query):
qryOne:-
SELECT [EMP ID], [DATE], [TIME],
(Select count(*) from [tblPunchCard] where [EMP ID] = a.[EMP ID] and [Date]+[Time] <= a.[Date]+a.[Time]) AS Num,
iif(Num mod 2 =1, "In" ,"Out") AS InOut,
iif(InOut="In", Num, Num-1) AS Pair
FROM [tblPunchCard] AS a;
qryTwo:-
SELECT [EMP ID], Min([Date]) AS [Clock-In Date],
format(Min([Date]+[Time]), 'Short time') AS [Clock-in Time],
iif(Max([Date]+[Time]) <> Min([Date]+[Time]), Max([Date]), Null) AS [Clock-out Date],
iif(Max([Date]+[Time])<>Min([Date]+[Time]), format(Max([Date]+[Time]) ,'Short Time'), Null) AS [Clock-out Time]
FROM qryOne
GROUP BY [EMP ID], Pair;
Run the second query.
(As clock-in date and clock-out date are different in the sample data, I have included [Clock-out Date] in the query result. You can delete it if you want to.)
Hello, I have a similar situation. However I have a table to each employee. The table is like:
IO DateIO TimeIO
IN 11/10/2004 09:02:00
OUT 11/10/2004 14:01:00
IN 11/10/2004 14:23:00
OUT 11/10/2004 20:27:00
IN 13/10/2004 08:32:00
OUT 13/10/2004 13:50:00
IN 13/10/2004 14:42:00
OUT 13/10/2004 16:23:00
As you can see I have 4 rows to calculate the time for a single day.
Any idea on how I could manage this?
