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

    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


  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    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
    rs2!UserID = vUserID
    rs2!TimeIn = vIn
    rs2!TimeOut = rs1CheckTime
    rs2!HoursDiff = DateDiff("h", vIn, rs1!CheckTime)
    vIn = null
    End If

    vIn = rs1!CheckTime
    vUserID = rs1!UserID

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 - Senior Forums