Access- Help needed writing a formula for 1 record to another

[rickes]

I am lost. I have an access database that I am keeping track of belt changes. I want it to automatically give me the total hours on a belt from the time it was put on to the time it was changed. It looks something like this (top row is column header):

EQUIPMENT BELT DATE HOURMETER REASON TOT.HOURS ON BELT
press 1 upper 12/01/04 100 torn
press 1 upper 1/30/05 150 torn
press 1 upper 3/15/05 250 plugged

What formula would I write in column(TOT.HOURS ON BELT)in a query or in a report to subract record 1 (HOURMETER) from record 2 (HOURMETER) and so on down the column everytime i add a new record to show me the TOT. HOURS ON BELT for each belt changed? For Instance, Record 1 should read 50 Hrs in TOT. HOURS ON BELT(150-100), Record 2, 100 Hrs, Etc.
I need all the help I can get.
Thanks so much..
RICKES

 

[Remou]

Hi
I do not know how much help this is. Say you added TotHours as a field.

Dim rs as Recordset
Dim intTotHours

Set rs = CurrentDB.OpenRecordset("Select * From tblBelt Order By [Date]")
rs.Movefirst
Do While Not rs.EOF()
  intTotHours = rs!HourMeter
  rs.Movenext 
  rs.Edit
  rs!TotHours = rs!HourMeter - intTotHours 
  intTotHours = rs!HourMeter
Loop

I have just typed this, so it is probably full of mistakes. It fills the data in from 2nd record on as there is nothing to subtract from the first record!
PS Might be best to rename date to prevent problems with names.