Good day,
Iam traying to get result of my record as below
If next record is biger than previous show "1"
If next record is same previous show "1"
If next record is smallest show "0"
if next record is same previous show "0"
Example
id Time Code Price Res
1 10:00:00 AM 1010 905
464 10:00:34 AM 1010 906
626 10:00:56 AM 1010 906
1523 10:02:16 AM 1010 904
1568 10:02:21 AM 1010 904
1569 10:02:21 AM 1010 905
the code gave me correct result for some filed and other incorrect !
Could you please check the code and correct it for me.
I have attached the DB for any correction
Note: the command bottons on Form1 of Forms Object.
Iam very appreciated for assistant . :)
..
Private Sub cmdUpdate_Click()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String
Dim Code As String
Dim Price As Long
'Dim Res As String
Dim Res As Variant
'Dim PrevRes As String
Dim PrevRes As Variant
Set cnn = CurrentProject.Connection
SQL = "Select [Code],[Price],[Res] from [tt]" & _
" order by [Code],[ID]"
rst.Open SQL, cnn, adOpenKeyset, adLockOptimistic
Do While Not rst.EOF
Code = rst![Code]
Price = rst![Price]
'PrevRes = ""
PrevRes = Null
rst.MoveNext
Do While rst![Code] = Code
'Res = IIf(rst![Price] > [Price], "1", IIf(rst![Price] < [Price], "0", IIf(rst![Price] = [Price], PrevRes, Null)))
Res = IIf(rst![Price] > [Price], "1", IIf(rst![Price] < [Price], "0", PrevRes))
rst![Res] = Res
PrevRes = Res
Price = rst![Price]
rst.MoveNext
If rst.EOF Then
Exit Do
End If
Loop
Loop
Set cnn = Nothing
Set rst = Nothing
MsgBox "Res field updated."
End Sub