1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

VB6 - selecting records from an access database

Discussion in 'Software Development' started by basy27, Oct 29, 2007.

Thread Status:
Not open for further replies.
  1. basy27

    basy27 Thread Starter

    Oct 16, 2007
    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
    z = 10
    End If

    On Error GoTo errorhandler

    p = 1

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

    Reccount = rs.RecordCount
    For i = 0 To Reccount
    If v = Trim(DTPicker1.Value) Then
    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
    v = Trim(Left$((Str(rs!DateTime)), z))
    v = Trim(Left$((Str(rs!DateTime)), z))
    End If


    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??

    Attached Files:

  2. metweek


    Jun 6, 2003
    Look into the MIN and MAX SQL functions.
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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/645224

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice