Need to find a previous record

[Mark6455]

I have an Access database for tracking costs and other data of my truck. One such record is when my engine goes into a regeneration mode. What I need to do is find the last record entered for a regen, then subtract the mileage from the PREVIOUS regeneration so as to get the miles between regenerations.

Regeneration data is stored in a record called "Regeneration"
Mileage is stored as "Start Mileage" and "End Mileage"

To summarize, I need the current regeneration mileage minus the previous regeneration mileage and list the number.
If I regened at 20,210 and the previous mileage Regen was 19,810, I need to show the difference which is 400 miles.

I don't know how to figure the previous number in a query.

Thanks in advance

 

[G37Sam]

You need an ID field in your Regen table - Using DAO: open that table in ascending order of that ID, scroll to the last record and then goto the previous record.

Another way is to use DMAX(RegenID,tblRegen) -1 but you'll have to make sure the ReGen ID increments 1 at a time using the BeforeInsert Event

 

[jdraw]

Might be helpful to you and readers if you showed your table design(s).

 

[Pat Hartman]

You can just find the max mileage for the truck since mileage always stays the same or goes up.

 

[arnelgp]

you can use DMax() function.
your Last Mileage:

dblLastMileage = DMax("[End Mileage]", "Regeneration")

dblDifference = CurrentMileageReading - dblLastMileage