Moving through Recordset & doing a record compare

[Guest_imported]

I'm a VBA newbie. I'm trying to figure out how to open a recordset based on a query (sort order is date/time). I need to move through the record set and compare a field value to the value in the previous record. If the comparison is say less than 12 hours I do a calculation based on that previous value and assign a calculatedvalue to the current record field. If the value is greater I do something else (actually get the value from a lookup in a lookup table). This is due to the fact that the value from the previous record determines the correct value in the current record (iterative loop). I need to go in order because theoretically you could be basing the current record value based upon the results of 2,3,4,....etc. records. Yet, if the interval from the previous record is greater than 12 hours the caculation is from a fresh "start". If your a diver a good example is the calculation of your ending dive group which in some instances is based on the previous dives ending group (you keep building nitrogen loading).

Help would be more than gratefully accepted. Many thanks !!

 

[B827]

I have modified a crude bit of code. This might give you the foundation for something to work with.

Dim MyDb As DAO.Database, Myrst As DAO.Recordset, Previousf1 As Variant, Previousf2 As Date
Dim ActionVal As Double, i As Double, TempDate As Date
Set MyDb = CurrentDb
Set Myrst = MyDb.OpenRecordset("SortQuery", dbOpenDynaset)
Previousf1 = ""
Previousf2 = Now()
i = 0
With Myrst
.MoveFirst
Do Until .EOF
i = i + 1
If Previousf1 = !field2 Then
If Previousf2 + 0.5 > !dandt Then
.Edit
!Action = i
.Update
End If
End If
Previousf1 = !field2
Previousf2 = !dandt
.MoveNext
Loop
.close
End With
Sandy

 

[Guest_imported]

Thanks. I'll give it a try.

Really appreciate it.