Date Difference between consecutive rows

[shod90]

Dear Gents,
Um creating attendance form that calculate the difference between the check in and check out , But i didn't know it's hard to find the difference between consecutive rows

below is a sample from my table data that i need to calculate the difference between them



what um expceted to see this result
05-03-2017 08:15 h:m
06-03-2017 08:50 h:m

And so on ....

[ranman256]

there is no query to do this. 1 row cannot see another row.
you need vb code. Date and time should have been in 1 single field.
something like:

Public Sub CalcElapse()
Dim rst   'As Recordset
Dim sSql As String
Dim bPrev As Boolean, bIn As Boolean

sSql = "select * from table"
Set rst = CurrentDb.OpenRecordset(sSql)
bPrev = False

With rst
    While Not .EOF
        vDat = .Fields("Date")
        vTim = .Fields("time")
        vio = .Fields("checktype")
        bIn = (vio = "I")
        
        Select Case True
           Case bIn And bPrev
              'error  2 INs
              GoTo skipIt
           
           Case bIn And Not bPrev
              vStart = vDat & " " & vTim
           
           Case Not bIn And bPrev
              vEnd = vDat & " " & vTim
           
           Case Not bIn And Not bPrev
              'error  2 OUTs
              GoTo skipIt
        End Select
         
        vElaps = DateDiff("n", vStart, vEnd)
                 
        If vEnd Then Debug.Print vStart, vEnd, vElaps
        vPrev = vIn
        
skipIt:
       .MoveNext
    Wend
End With

Set rst = Nothing
End Sub

[CJ_London]

you can use a query,


SELECT I.Date, format(O.Time-I.Time,"hh:nn") as Diff
FROM myTable I INNER JOIN myTable 0 ON I.Date=O.Date
WHERE I.CheckType="I" AND O.CheckType="O"

Any dates without both In and Out will be excluded, so if you have someone who checked in yesterday and checked out today would not be reported - you will need a modification to the above where, as Ranman suggested, it would be a lot easier if the date and time fields are combined (you can still display the same field twice, once formatted as a date and once as time). Also the hh:nn format only works for periods of less than 24 hours, otherwise you need to use a UDF to convert to a display you require.

Similarly, if you have multiple ins and outs for the same day, you will get multiple returns.

Note that date and time are both reserved words, so using them may generate unexpected results. Strongly recommend you change them to something more meaningful

[shod90]

there is no query to do this. 1 row cannot see another row.
you need vb code. Date and time should have been in 1 single field.
something like:

Public Sub CalcElapse()
Dim rst   'As Recordset
Dim sSql As String
Dim bPrev As Boolean, bIn As Boolean

sSql = "select * from table"
Set rst = CurrentDb.OpenRecordset(sSql)
bPrev = False

With rst
    While Not .EOF
        vDat = .Fields("Date")
        vTim = .Fields("time")
        vio = .Fields("checktype")
        bIn = (vio = "I")
        
        Select Case True
           Case bIn And bPrev
              'error  2 INs
              GoTo skipIt
           
           Case bIn And Not bPrev
              vStart = vDat & " " & vTim
           
           Case Not bIn And bPrev
              vEnd = vDat & " " & vTim
           
           Case Not bIn And Not bPrev
              'error  2 OUTs
              GoTo skipIt
        End Select
         
        vElaps = DateDiff("n", vStart, vEnd)
                 
        If vEnd Then Debug.Print vStart, vEnd, vElaps
        vPrev = vIn
        
skipIt:
       .MoveNext
    Wend
End With

Set rst = Nothing
End Sub

Actually i appreciate your efforts , But don't know where to put this code and when ? .. I know that i should create a module but then ?

you can use a query,


SELECT I.Date, format(O.Time-I.Time,"hh:nn") as Diff
FROM myTable I INNER JOIN myTable 0 ON I.Date=O.Date
WHERE I.CheckType="I" AND O.CheckType="O"

Any dates without both In and Out will be excluded, so if you have someone who checked in yesterday and checked out today would not be reported - you will need a modification to the above where, as Ranman suggested, it would be a lot easier if the date and time fields are combined (you can still display the same field twice, once formatted as a date and once as time). Also the hh:nn format only works for periods of less than 24 hours, otherwise you need to use a UDF to convert to a display you require.

Similarly, if you have multiple ins and outs for the same day, you will get multiple returns.

Note that date and time are both reserved words, so using them may generate unexpected results. Strongly recommend you change them to something more meaningful

Appreciate your reply,Actually the source field already is combined date and time in same field,I separated it with format function only..Still i don't know what to do actually

[CJ_London]

already is combined date and time in same field

for a single field use

SELECT datevalue(I.Date) as myDate, format(O.Date-I.Date,"hh:nn") as Diff
FROM myTable I INNER JOIN myTable 0 ON datevalue(I.Date)=datevalue(O.Date)
WHERE I.CheckType="I" AND O.CheckType="O"

1. in the above code, change myTable to the name of your table, Date to the name of your combined date/time field
2. open a new query
3. go to the sql window
4. copy and paste the revised code to replace anything that is currently there
5. run the query to check it works
6. go to design view to see the query builder view of the query