Tech Support Guy banner
Status
Not open for further replies.
1 - 2 of 2 Posts

· Registered
Joined
·
5 Posts
Discussion Starter · #1 ·
Hi all,
i am creating a small program which will use the database of my company access control program to calculate time and attendance details for employees. ie it will calculate overtimes and undertimes etc.
Employees scan their access cards at the door when antering and leaving the building.
a picture of The original database is attached (64kb).
Now during the day a single employee might scan in and out more than 10 times but i am only interested in the 1st and last scans, so i can calculate the time difference between these scans and thereby calculate overtime undertime etc.

So far i have been able to extract details for 1 specified date and copy them to a new database hwere i would now like to sort and delete all transactions which are not the first and last for a specific operator.

Here's part of my code :


Private Sub Form_Load()
Set db = OpenDatabase("C:\Documents and Settings\sebastian\My Documents\xxx\IT Project 2\ssAccLog.mdb")
Set dbTemp = OpenDatabase("C:\Documents and Settings\sebastian\My Documents\xxx\IT Project 2\DbTemp.mdb")

Set rs = db.OpenRecordset("ssAccessLog")
Set rsTemp = dbTemp.OpenRecordset("tbltemp")

Private Sub Command1_Click()
Dim v As String
Print rsTemp.RecordCount
If Option1.Value = True Then
z = 9
Else
z = 10
End If

On Error GoTo errorhandler

rs.MoveFirst
p = 1

v = Trim(Left$((Str(rs!DateTime)), z))

Reccount = rs.RecordCount
For i = 0 To Reccount
If v = Trim(DTPicker1.Value) Then
rsTemp.AddNew
rsTemp!RecNo = p
p = p + 1
rsTemp!Logid = rs!Logid
rsTemp!Date = v
rsTemp!Name = "test"
rsTemp!Time = Right$(rs!DateTime$, 11)
rsTemp!tagid = rs!tagid
rsTemp.Update
rs.MoveNext
v = Trim(Left$((Str(rs!DateTime)), z))
Else
rs.MoveNext
v = Trim(Left$((Str(rs!DateTime)), z))
End If

Next

errorhandler:
MsgBox "Data successfully imported.", vbInformation, "Pluczenik Botswana(PTY)Ltd."

End Sub

Can anyone PLEASE help me out and explain how i could extract only the 1st and last scans for each employee??
 

Attachments

1 - 2 of 2 Posts
Status
Not open for further replies.
Top