Calculate time between start and finish within a recordset

[NKA]

Hi.

Wondered if anyone can help me. I have a feeling I need the "Do While..."

To give the overall picture, I have a table which contains GPS tracking data. I need to be able to work out how long the vehicle was stopped at all the locations visited so I can create a bar graph showing productive/non-productive time for each vehicle.

I have a field called [activity] which tells me when the ignition was on/off. I have a field called [DateTime] in long date format, a text field [OnSiteTime] which tells me days, hours, minutes on site (eg. 0d 14h:35m) and a field for [Location].

Firstly, I need to be able to calculate the total time the vehicle was used during the day - so I have done this by taking FirstOf[DateTime] where [Activity] = "Iginition On" from LastOf[DateTime] where [activity]="ignition Off".

Productive time is assumed when the vehicle is not in use. Here, I need to find the total time at each [location]. The text field doesn't allow me to calculate, so I think I really need some code that will look through all the records (in ascending datetime order) and do a calculation of time for each [activity] changing ingition status (on/off). Bearing in mind the [activity] field also contains activities other than ignition on/off.

If anyone understand what I am trying to achieve, I would be most grateful!
Cheers
NKA



NKA

The answer's always easy - if you know it! ;-)

 

[PHV]

The text field doesn't allow me to calculate
What about something like this ?
nDD = Left(OnSiteTime, InStr(OnSiteTime, "d") - 1)
nHH = Left(Right(OnSiteTime, 7), 2)
nMN = Left(Right(OnSiteTime, 3), 2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244