Calculate time between start and finish within a recordset

[NKA]

Hi. I am hoping someone will understand what I am looking for below.

I have a linked table with GPS tracking data for vehicles. I need to produce a bar graph which shows the total time (calculated from last ignition off - first ignition on) divided into productive and non-productive time.

I can calculate the total time using the [datetime] field based on the [activity].

The next bit is where I fall over somewhat!

There is a field called [onsitetime], which is a text field (eg: 0d 14h:35m) but I cannot use this as I need actual TIME to calculate.

I believe that I need some code that will look through all the records and somehow calculate the total time on sites based on [activity] ignition off - ignition on for each site (by vehicle)

I am happy to email someone the sample file (currently in csv format) if they can help!

Any takers?
Cheers

NKA

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

 

[Frink]

Hallo,

I'm a bit confused as to your table structure?

- Frink

 

[NKA]

The table is a CSV file imported into Access 97.

The fields are:
[vehicle] text
[activity] text
[datetime] date/time (ie: 16/08/04 2:35:00 p.m.
[onsitetime] text (ie: 0d 14h:35m
[location] text

The table is a raw data dump from the GPS system

NKA

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

 

[Frink]

Hallo,

Could you paste about 20 records from the CSV to give us a bit more to go on?

- Frink

 

[NKA]

Not sure if this is any use. If not, let me have an email address to send it to.

VEHICLE ACTIVITY DATETIME ONSITE LOCATION
ABC 123 Ignition On 16/08/2004 7:27 0d 14h:35m James St, Claudelands, Hamilton City, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 7:32 Howell Ave, Hillcrest, Hamilton City, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 7:36 Cambridge Rd, Silverdale, Hamilton, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 7:39 State Highway 1, Mercer, Waikato, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 7:43 State Highway 1, Pukeroro, Waipa, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 7:47 Achilles Ave, Cambridge, Waipa, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 7:51 State Highway 1, Piarere, Waipa, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 7:54 State Highway 1, Piarere, Waipa, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 7:57 State Highway 1, Piarere, Waipa, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 8:00 State Highway 29, Piarere, Matamata-piako, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 8:03 State Highway 29, Hinuera, Matamata-piako, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 8:07 State Highway 29, Hinuera, Matamata-piako, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 8:10 State Highway 29, Te Poi, Matamata-piako, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 8:13 State Highway 29, Te Poi, Matamata-piako, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 8:17 State Highway 29, Te Poi, Matamata-piako, Waikato, New Zealand
ABC 123 Timed Update 16/08/2004 8:20 State Highway 29, Lower Kaimai, Western Bay Of Plenty, Bay Of Plenty, New Zealand
ABC 123 Timed Update 16/08/2004 8:24 State Highway 29, Lower Kaimai, Western Bay Of Plenty, Bay Of Plenty, New Zealand
ABC 123 Timed Update 16/08/2004 8:27 State Highway 29, Lower Kaimai, Western Bay Of Plenty, Bay Of Plenty, New Zealand
ABC 123 Timed Update 16/08/2004 8:30 State Highway 29, Lower Kaimai, Western Bay Of Plenty, Bay Of Plenty, New Zealand


NKA

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

 

[Frink]

Hallo,

You've only given me two types of records. Not much help really, is it?
I think from the data provided that the vehicle had been idle for 14 hours and 35 mins, then was started at 7:27 at James St, then drove to Lower Kaimai.
What are the records like when the vehicle got to its destination? What is the data when he/she switched the ignition off and back on again. when I said about 20 records, I meant 20 records incorporating the actual useful ones, not 19 all the same.

Assuming that the ignition is turned off while on site, you should be able to calculate the times from the ignition on/off ONSITE fields, and can ignore the Timed Updates (although they are 'good' for spying on the driver)

- Frink

 

[NKA]

Hi Frink,

The records show exactly the same, with the activity field reading something different - like "iginition on" and "ignition off".

This is why I am struggling to get the answers as the date/time is all in the one field - regardless of activity.

Would it be easier if I emailed you the spreadsheet?

Cheers


NKA

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

 

[Zion7]

NKA, I am a little confused, with your objective. What I did catch, I think, is your desire to do a calculation with [OnSiteTime]. But, because it is neither a Date or Time format, you can not use any Access, time calculating functions.
In such a case, If I'm correct, I would re-convert the string, into a recognizable Date/Time Format.

Dim st1, st2 As String

st1 = Mid("0d 19h:35m", 4, 2) & Mid("0d 19h:35m", 7, 3)
st2 = Mid("0d 23h:22m", 4, 2) & Mid("0d 23h:22m", 7, 3)

Dim dStart, dFinish As Date, sTotalMinutes As Single
dStart = Format(st1, "h:n")
dFinish = Format(st2, "h:n")

sTotalMinutes = DateDiff("n", dStart, dFinish) 'gives results in minutes

Debug.Print sTotalMinutes

With this, can you loop through all records, collecting total time on site in minutes, (maybe convert to hours?)

...sorry, if I'm way off on this?

Good Luck either way!

 

[Frink]

Hallo again,

So a set of records for a day would be like:
Ignition On 16/08/2004 7:27 0d 14h:35m Home
Ignition Off 16/08/2004 8:27 0d 01h:00m Job1
Ignition On 16/08/2004 12:27 0d 04h:00m Job1
Ignition Off 16/08/2004 12:37 0d 00h:10m Lunch
Ignition On 16/08/2004 13:17 0d 00h:40m Lunch
Ignition Off 16/08/2004 13:27 0d 00h:10m Job2
Ignition On 16/08/2004 16:27 0d 03h:00m Job2
Ignition Off 16/08/2004 17:27 0d 01h:00m Home
(only including the Ignition ones)

Is that what the data looks like?

If it is then the onsitetime of the IgnitionOn records are the hours worked and the IgnitionOff ones are the time spent travelling, if you ignore the first IgnitionOn and exclude lunch.
You don't actually need to use the time of day at all (although it would be useful to keep)

To convert '0d 14h:35m' to a number of minutes or time format it would be best to write a function to use Val, Mid and Instr to extract the data.

- Frink

 

[NKA]

Thanks Frink,

Yes, you are kind of on the right track. I have had all sorts of problems trying to convert the data type, but will try your code. The looping through bizzo is exactly what I couldn't get my head round.

Thank you so much - will keep you posted on progress!

Cheers

NKA

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

 

[Zion7]

NKA, It sounds like your last comments, were addressing me.

If so, this is one way of looping, modifying the code I gave previously. This procedure, simply adds All the OnSiteTime records. Some of my criteria for the recordset may be wrong (according to the info, you desire), but, you'll get the idea...

Public Function TotalOnSiteTime()As String
Dim rec As ADODB.Recordset, SQL, iTotalOnSiteTime As String
Dim iHours, iTotalHours, iExtraHours iMinutes, iTotalMinutes, iRemaingMinutes As Integer
Set rec = New ADODB.Recordset

'open recordset, excluding (undesired?) records.
'(change table & field names accordingly)


SQL = "SELECT * FROM tblGPSTracking WHERE txtActivity = 'Ignition On' Or txtActivity = 'Ignition Off'"

rec.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rec.EOF

iHours = Mid(rec!OnSiteTime, 4, 2)
iMinutes = Mid(rec!OnSiteTime, 8, 2)

iTotalHours = iTotalHours + iHours
iTotalMinutes = iTotalMinutes + iMinutes

rec.MoveNext
Loop

iExtraHours = Int(iTotalMinutes / 60)
iRemainingMinutes = iTotalMinutes Mod 60

iTotalHours = iTotalHours + iExtraHours

iTotalOnSiteTime = "od " & iTotalHours & "h:" & iRemainingMinutes & "m"

rec.Close: Set rec = Nothing

TotalOnSiteTime = iTotalOnSiteTime

End Function

...Paste into standard module, in immediate window type, ?TotalOnSiteTime ...Then press enter.

Hope this helps, Good Luck!