VB6 - selecting records from an access database

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

basy27

Thread Starter
Joined
Oct 16, 2007
Messages
5
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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top