How to compare data between 2 consecutive records in a table

[kimmer]

I have a database that records results from devices that uses an algorthmic code as its meter. I capture from and to readings. I would like to compare readings between 2 consecutive records to see if there is a gap n the readings. Therefore, I would like to compare the "TO" reading in one entry to the "FROM" reading in another for gaps.

[Oralloy]

Well, do a select of your records, sorted on timestamp, and walk them to inspect for differences.

Pseudo code:

rs = SELECT * FROM [table] ORDER BY [timestamp]

value1 = rs("value1")
value2 = rs("value2")
etc...

next rs

for each remaining rs
  new1 = rs("value1")
  new2 = rs("value2")

  display (new1 - value1) and (new2 - value2)

  value1 = new1
  value2 = new2

  next rs
end

done

Cheers!

[kimmer]

Thanks for the code. It makes sense. I have programmed in the past and am new to Access. I do not understand where this code would be inserted. I have done macros on events for forms, but not on reports. How or where would I insert this code?

[Oralloy]

kimmer,

It's pseudo code, not real access code.

I assume that you are hanging your differences code off of a form button or other event handler.

If you need help just getting off the ground, I'm probably not the best coach. Still, I'll help you as best I can.

Luck!

[kimmer]

Orally,
Just got back to this. I am performing this code in a report not a form. What event handler would you suggest I use to connect this code to?

[Oralloy]

Kimmer,

Is the report simply being run, and the differences (i.e. missing record) shown for all records, except the first?

As I said earlier, I may not be the best coach. I really am unfamiliar with reporting in Access.

I tried with the difference code in Detail_Paint(), like so, but I'm not sure that is the best way to go about it.

Private Sub Detail_Paint()
  On Err GoTo ohNo

  Dim status1 As Variant
  status1 = Me![Status]

  If IsNull(status0) Then
    Text2 = "s0: Null" & ASCII.CRLF & _
            "same"
  ElseIf (status0 = status1) Then
    Text2 = "same: " & status0
  Else
    Text2 = "diff: " & status0 & " / " & status1
    status0 = status1
  End If

  Exit Sub

ohNo:
  MsgBox "Error here:" & ASCII.CRLF & Err.Description
End Sub

Private Sub Report_Open(Cancel As Integer)
  status0 = Null
End Sub

The problem is that my initialization code in Report_Open(... ) is in the wrong place. I'm not sure where to put it.