Reset Field Value (1 Viewer)

[MikeG]

I'm trying to setup a Company vehicle database. I have 3 tables; tblEmployee, tblCar, tblUsage, that are primary. The Usage table includes the ending odometer reading from the car while the tblCar includes the beginning odometer reading when the car was put in service. The mileage logs are turned in weekly. I'm having problems getting one of the reports in the format my supervisor would like. They would like a detailed report sorted by Vehicle_ID, then month, then display the date, the Empl_ID, the ending odometer reading for the week, and the difference in miles driven from the prior week, all in the detail section. I've played with the queries and all I've been able to come up with are running totals because of my reference back to the beginning odometer reading. I thought I was onto a solution from an earlier post that used DLookup and an autonumber field to look back to the previous record. I made a slight modification to the code for my field names and all is well and good until the vehicle_ID changes: My calculated difference is way off due to the wrong reference to the previous car's last ending_odometer reading.
It's getting the thing to total the mileage and reset when the section (vehicle_ID) changes. It might also be nice to have a reset at the month level but at this point I'll settle for just the veh_ID level!

I did search all posts in all forums related to the term mileage and came up with 28 hits. Unfortunately the one post that looked to be identical to mine went unanswered. I'm reposting with the hope that someone else has run across this "challenge" since the original post was made.

 

[Rich]

Use the sorting and grouping options on a report and the PrevRecVal function, it's faster than DLookup or use the running sum property of a text box.

[This message has been edited by Rich (edited 05-31-2002).]