running difference between adjacent records in report

[TheWireMonkey]

I need to calculate the "in-between-time" for two records.

Example: process 1 starts at 11:00 a.m. and ends at 11:15 a.m.; process 2
starts at 12:30 p.m. and ends at 12:45 p.m.

I need to calculate the difference between the end of process 1 and the
beginning of process 2 in an Access 2003 report.

This is sort of the opposite of a running sum, but I cannot figure out if I
can manipulate the running sum function to find the answer.

 

[Allen Browne]

You can do this by declaring a variable to hold the ending date/time from
the previous record, so you can compare it to the starting time from the
current record. You use the report's events to assign this.

Steps:

1. In the General Declarations section of the report's module (at the time,
with the Option statements), add the line:
Dim mvarEnd As Variant

2. In the Open event of the report, initialize your variable to Null:
Private Sub Report_Open(Cancel As Integer)
mvarEnd = Null
End Sub

3. In the Print event procedure of the Detail section, assign the value of
the end date/time field to your variable (substituting your field name for
TheEnd):
Private Sub Detail_Print(...
mvarEnd = Me.[TheEnd]
End Sub

4. In the Format event procedure of the Detail section, assign the number
minutes to the unbound text box you set up to display this. (TheBreak is the
unbound text box; TheStart if the name of your date/time field.)
Private Sub Detail_Format(...
If IsNull(mvarEnd) Or IsNull(Me.[TheStart]) Then
Me.[TheBreak] = Null
Else
Me.[TheBreak] = DateDiff("n", mvarEnd, Me.[TheStart])
End If
End Sub