Calculating vehicle individual journey mileage over a given time

[Martin001]

Hi
Here is a query I have produced from a vehicle tracker database table. What I want to do is to calculate mileage of each journey over a time period based on the start and stop in the reason field. I have highlighted what I want in the calc field, so I know the mileage of each journey taken. Can anyone help I have no idea how the achieve this.
Thanks
Martin

Vehicle	Time_Date	Street Name	Distance (mi)	Reason	Calc Field
xxxxxx	06/05/14 17:08:20		0	Ignition On
xxxxxx	06/05/14 17:08:31		0	Start
xxxxxx	06/05/14 17:08:59		0.2	Error report
xxxxxx	06/05/14 17:09:54		0.2	Time Send
xxxxxx	06/05/14 17:10:54		0.5	Time Send
xxxxxx	06/05/14 17:38:57		0.4	Time Send
xxxxxx	06/05/14 17:39:23		0	Ignition Off
xxxxxx	06/05/14 17:39:23		0	Stop	1.3
xxxxxx	06/05/14 17:39:23		0.1	Acc. Summary
xxxxxx	06/05/14 17:39:23		0	Bus Ignition Off
xxxxxx	06/05/14 17:50:35		0	Wakeup
xxxxxx	07/05/14 17:07:19		0	Bus Ignition On
xxxxxx	07/05/14 17:07:26		0	Ignition On
xxxxxx	07/05/14 17:07:27		0	Start
xxxxxx	07/05/14 17:08:05		0.2	Error report
xxxxxx	07/05/14 17:09:00		0.3	Time Send
xxxxxx	07/05/14 17:13:01		0.2	Time Send
xxxxxx	07/05/14 17:14:01		0.2	Time Send
xxxxxx	07/05/14 17:31:02		0.4	Time Send
xxxxxx	07/05/14 17:32:03		0.2	Time Send
xxxxxx	07/05/14 17:32:08		0	Acc. Summary
xxxxxx	07/05/14 17:32:08		0	Stop	1.5
xxxxxx	07/05/14 17:32:08		0	Bus Ignition Off
xxxxxx	07/05/14 17:32:08		0	Ignition Off
xxxxxx	08/05/14 17:00:42		0	Time Send

[ranman256]

you could scan thru each record , counting as you go , then update the record....

Public Sub Milage()
Dim rst As Recordset
Dim dMils As Double, dDist As Double
On Error GoTo errMakeTbl
'query to sort data by vehicle/time
Set rst = CurrentDb.OpenRecordset("qsCarLog")
With rst
    While Not .EOF
       vRsn = .Fields("Reason").Value & ""
       dDist = .Fields("Distance (mi)").Value & ""
       If vRsn = "Start" Then dMils = 0
       dMils = dMils + dDist
       
       If vRsn = "End" Then
           .Edit
           .Fields("Calc Field").Value = dMils
           .Update
       End If
      .MoveNext
    Wend
End With
rst.Close
Set rst = Nothing
End Sub

[Martin001]

Thanks ranman256

I thinks I can make That work