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"


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
      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]
         If rst.EOF Then
            Exit Do
         End If
   Set cnn = Nothing
   Set rst = Nothing

   MsgBox "Res field updated."

End Sub

Iam sorry my expamle was not cleared.
Asum that I have this fields: Id,Code,Price,Res
the "res" field will show the vb code result
if the current price = 905 then I input a new entery with price 905 the Res filed should show "0" because the price same as previous price. then when I input a new entry with price 906 the result should show "1" because the previos record is less.

This article has been dead for over six months. Start a new discussion instead.