Subtracting Time Values & Adding to a Table

[krizma5683]

I've created some code to add calculated fields to my table. I have one field that subtracts dttime2 from dttime1 to come up with the elapsed time. When I first wrote this it was working correctly, however when I added in the calculation for the columns breaks and readings my elapsed column quite working correctly. I think I'm overlooking something very obvious in my code, can someone please help?

Private Sub Computed_Rte_Time_Fields()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT RouteTimes_IPL.* FROM RouteTimes_IPL ORDER BY [Read Date],[Meter Reader ID],[Read Time];"

Dim strRoute1, strRoute2 As String 'Route ID
Dim intReaderID1, intReaderID2, intreading, intskipcode As Integer 'Meter Reader ID
Dim dtDate1, dtDate2, dtTime1, dtTime2, dtelapsed, dtbreaks As Date 'Read Date & Read Time

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveLast
    .MoveFirst 'move to rowA
While Not rst.EOF

'Create [RTE-DIV] column
    strRoute1 = rst(1)
    div_rte = Right(strRoute1, 5)

'Create [Elapsed] column
    intReaderID1 = rst(2)
    dtDate1 = rst(8)
    dtTime1 = rst(9)

.MoveNext 'move to rowB
    If Not rst.EOF Then
    strRoute2 = rst(1)
    intReaderID2 = rst(2)
    dtDate2 = rst(8)
    dtTime2 = rst(9)
    End If
    
.MovePrevious

If intReaderID1 = intReaderID2 Then
    dtelapsed = dtTime2 - dtTime1

End If

'Create [Breaks] column
If dtelapsed > 0.010416 Then
    dtbreaks = dtelapsed
Else: dtbreaks = 0
End If

'Create [Readings] column
    intskipcode = rst(10)
    If intskipcode > 0 Then
    intreading = 0
    Else: intreading = 1
    End If

CurrentDb.Execute "Update RouteTimes_IPL set [DIV-RTE] = '" & div_rte & "',[Elapsed]='" & dtelapsed & "',[Breaks]='" & dtbreaks & "',[Readings] = '" & intreading & "' where [Autonumber]= " & rst(0)
rst.MoveNext 'move to next record
RecordCount = RecordCount + 1
    
Wend
End With
End Sub

 

[lameid]

Try looking up Datediff in help.

 

[krizma5683]

I couldn't get that to work, but I was able to use the format() function to format my date prior to committing to to the database [dtelapsed] and it now puts the value in the table as expected.

Thanks for the post though...