Results 1 to 2 of 2
  1. #1
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134

    calculate the difference between IN and OUT for cumulative record

    Dear Gents,


    I have a table which contains attendance records ( Exported from the attendance machine )
    I need to calculate the difference between IN and OUT for each record , But the problem they are cumulative records !
    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	26.1 KB 
ID:	28046

    Attached here with the database with the existing table in the screenshot , Hope that anyone can help

    attBackup.mdb

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I guess you have to assume the first time of the day is "In". So In, Out, In, Out, etc? Create new table tblHoursDiff with fields HoursDiffID(auto), UserId(numeric), TimeIn(datetime), TimeOut(datetime), HoursDiff (numeric). This is not tested, just throwing it out there.

    Dim db as database, rs1, rs2 as Recordset, vUserID as Numeric, vIn as DateTime
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Select * from tblAttendance Order By UserID, CheckTime")
    Set rs2 = db.OpenRecordset("tblHoursDiff")

    Do Until rs1.EOF
    If vUserID = rs1!UserID
    If Isnull(vIn) then
    vIn = rs1!CheckTime
    Else
    rs2.AddNew
    rs2!UserID = vUserID
    rs2!TimeIn = vIn
    rs2!TimeOut = rs1CheckTime
    rs2!HoursDiff = DateDiff("h", vIn, rs1!CheckTime)
    rs2.Update
    vIn = null
    End If

    Else
    vIn = rs1!CheckTime
    Endif
    vUserID = rs1!UserID
    rs1.MoveNext
    Loop

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-02-2016, 09:39 AM
  2. Replies: 5
    Last Post: 01-21-2016, 02:52 PM
  3. Calculate difference from last value
    By dilbert in forum Reports
    Replies: 4
    Last Post: 01-17-2016, 08:06 AM
  4. Calculate time difference
    By desireemm1 in forum Access
    Replies: 19
    Last Post: 10-27-2014, 12:06 PM
  5. Replies: 1
    Last Post: 02-12-2013, 03:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums