Travel time calculations between rows and columns

[hiaber]

I'm building a time sheet for my business - I'm a crane truck driver - I track my time as such:

Customer | Arrive time | Depart time |expr1 (hidden) _| Stop time___ |
Cust 1___ | 09:00 ___ | 09:35_____ |<value in mins> | ##hrs ##min. |
Cust 2___ | 10:10 ___ | 10:50_____ |<value in mins> | ##hrs ##min. |
Cust 3___ | 11:05 ___ | 11:30_____ |<value in mins> | ##hrs ##min. |

** underscores "_" are only shown to align columns in this post**

I've managed to create a query that gives me the total time on each site:
Expr1: DateDiff("n",[Arrive time],[Depart time]) -->>
Stop time: Int([expr1]/60) & " Hrs " & Format([expr1] Mod 60,"00") & " min. "

What I need to do is get a calculation of the travel time between each stop - ie: the amount of time between 9:35 and 10:10, and 10:50 and 11:05.

I'm a bit of a greenhorn when it comes to access, but i'm plucking through it. I'm using Acess 2007.

Is this possible with MS Access?



Any help or insight would be extremely appreciated!

and yes, you may have seen this posted on other access forums - i'm yet to get a reply, so i thought i'd post in a couple different places!

[llkhoutx]

Databases can''t calculate values among different rows.

I'd save the current row field values in the tag property for the relative fields of your form, initially set at null, then use that tag property in the calculate of successive rows. You also got to handle beginning and end of day and maybe customer.

Ex:
Initializing tag on some event

me.departtime.tag=Null 

On form OnCurrent event

TravelTime = datediff(me.arrivetime,me.departtime.tag 

On form Before_Update event

me.departtime.tag = me.departtime 

[hiaber]

I was able to use the Dlookup function to grab the info from the previous record and calculate the difference using that data in the next record! works like a charm!

[RuralGuy]

Excellent solution! There are other ways as well. Are you ready to follow the link in my sig and mark this thread as Solved?

[mrnikeswsh]

Hey, not sure if it would help, but I have the code somewhere in one of my databases for a distance calculation based on zipcode to zipcode. I work for a company that does 'service work' and with the high gas fee's we needed to know the distance from 2 locations to calculate a gas surcharge. (Its not great, because it is strait line of sight) but it helps. I think I convert each zip to lat/long then have a long formula to calc the distance.