Retrieve & insert previous value in recordset 1

[BusMgr]

I have created a recordset based on a table with a WHERE clause. I have four fields in the table:
WellID1
Days1 (rst(2))
MeasDepth1 (rst(3))
Cost1

There are some records in the recordset where the MeasDepth value is null. If it is null for the record corresponding to Days1 = 1, I want to set the MeasDepth =0, else if the value for Days1 > 1, I want to set MeasDepth to the previous record value. I have the first part (where Days1 = 1), but I cannot figure out how to accomplish the second one.

My current code as follows -


Set rst = db.OpenRecordset("Select * from tblTmpDailyCostMP WHERE [WellID1] = '" & strCriteria & "';")
With rst
.MoveFirst
Do While Not .EOF
If rst(2) = 1 And IsNull(rst(3)) Then
.Edit
![MeasDepth1] = "0"
.Update
ElseIf rst(2) > 1 And IsNull(rst(3)) Then
.Edit
![MeasDepth1] = ????
.Update
' .Fields("MeasDepth1") = 2
End If
'Skip past the current record & keep searching
.MoveNext
Loop
'Commit all the changes
'.Update
End With

 

[Darrylles]

Hi,

And in which field is 'Days1' ?

Or, in which table is the 'Days1' field?

It might help.

Regards,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@hotmail.com

 

[PHV]

Try something like this:
.MoveFirst
savMD1 = ""
Do While Not .EOF
If rst(2) = 1 And IsNull(rst(3)) Then
.Edit
![MeasDepth1] = "0"
.Update
ElseIf rst(2) > 1 And IsNull(rst(3)) Then
.Edit
![MeasDepth1] = savMD1
.Update
End If
savMD = rst(3)
.MoveNext
Loop


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BusMgr]

All four fields are in the tblTmpDailyCostMP table. I haven't yet tried PHV's solution, but I see where it should work. I'll try it and let you know.

Thanks
BusMgr