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

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

    Attached Files:

  2. metweek

    metweek

    Joined:
    Jun 6, 2003
    Messages:
    125
    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