It was the Worst of Times: Compare Records

[Shilo]

Hi All,

I'm working on a script that is trying to compare the field values in the current record to the record previous in Microsoft Access. If the fields are different then I want to perform a calculation and store the result in the "Duration" field.

So essentially if the ID in the current record (currID) is equal to the ID in the row before it (prevID) and the current beginning encounter date (currEncBegin) is equal to the previous beginning encounter date (prevEncBegin) and the current encounter end date (currEncEnd) is equal to the previous encounter end date (prevEncEnd) then subtract the value of the current order date (currOrder) from the previous order (prevOrder) and store that value in the duration field (duration).

I'm very new to Access so my attempts are just a shot in the dark, but I was hoping that the knowledgeable people in this forum could provide me some guidance.

Current script is below

Function CalcDuration()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim EncounterBeginDate As Date, ID As String, EncounterEndDate As Date, OrderCreation As Date, Duration As Long
Dim currID As String, prevID As String, currEncBegin As Date, prevEncBegin As Date, currEncEnd As Date, prevEncEnd As Date, currOrder As Long, prevOrder As Long

Set rs1 = CurrentDb.OpenRecordset("Shilo'sTable", dbOpenDynaset)
Set rs2 = rs1.Clone
rs1.MoveFirst
rs2.Bookmark = rs1.Bookmark
rs2.MoveNext
'x = 0
'rs1.MoveFirst 'MOVE TO THE FIRST RECORD
With rs1 'OPENS TABLE
Do Until .EOF
currID = rs2.Fields("ID").Value
prevID = rs1.Fields("ID").Value
currEncBegin = rs2.Fields("EncounterBeginDate").Value
prevEncBegin = rs1.Fields("EncounterBeginDate").Value
currEncEnd = rs2.Fields("EncounterEndDate").Value
prevEncEnd = rs1.Fields("EncounterEndDate").Value
currOrder = rs2.Fields("OrderCreation").Value
prevOrder = rs1.Fields("OrderCreation").Value
If currID = prevID Then
'And_
'currEncBegin = prevEncBegin And_
'currEncEnd = prevEncEnd Then
Duration = currOrder - prevOrder
End If
rs1.MoveNext
rs2.MoveNext
Loop
End With
rs2.Close
rs1.Close

Thanks so much for the help.

Sincerely,
Shilo

 

[plog]

First and foremost, there is no order in a table. There is no first, no next, no previous, no second to last, no 32nd. Order exists in data only when you explicitly use the ORDER BY clause of a SELECT query. When you open your table your data may appear in some order, but you cannot count on that being so every time, especially when using VBA to read in your data.

Second, you usually shouldn't store calculated values. What you normally do is use queries to calculate that value whenever you need it and then reference that query to obtain that data.

Third, you kind of lost me. It sounds like you want a running balance, but I'm not totally sure. Could you post some sample data from your table and then what that data should look like after manipulating it the way you intend?

 

[NDuarte]

What is the end result supposed to be?

A query would probably be the best thing to do to get the inforamtion if I am understanding this correctly.

If we know what the end result is supposed to be, we may be able to provide so assistance.

Thanks!