retrieving field data from a previous record in a report

[smileyx]

I am attempting to produce a customer call report system.<br>
<br>
The idea being whenever a customer visit is made details of the visit are logged and a report produced at the end of the week.Each call is stored as a record.<br>
<br>
One piece of data the form asks for is &quot;mileage on arrival at customer&quot;. <br>
<br>
However on the report for each customer call I wish to display &quot;mileage on arrival&quot; - no problem, but also &quot;mileage on leaving last call&quot; which is the &quot;mileage on arrival&quot; figure in the previous record.<br>
<br>
Thanks for your help

 

[DougP]

One way this can be accomplished is with a Function<br>
which will involve knowing exactly which record you are on and which is the previous record. The function will be in the report fields &quot;Control Source&quot; for say &quot;Mileage Driven&quot;<br>
<br>
You need a specific criteria which only gives you the previous record with out fail for an SQL statement.<br>
<br>
Here is a rough example<br>
----------------------<br>
Public Function MileageDriven(RecordIDPrevious)<br>
Dim db As Database, rst As Recordset, SQL As String<br>
Set db = CurrentDb<br>
' SQL string.<br>
SQL = &quot;SELECT * FROM YourTable WHERE RecordIDPrevious = &quot; & RecordIDPrevious<br>
Set rst = db.OpenRecordset(SQL)<br>
MileageDriven = rst.Fields(&quot;Mileage&quot<br>
End Function<br>
-----------------<br>
Depending on how many records you have in your report this could be slow. <br>