0

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 0
 464 10:00:34 AM 1010 906 1
 626 10:00:56 AM 1010 906 1
1523 10:02:16 AM 1010 904 0
1568 10:02:21 AM 1010 904 0
1569 10:02:21 AM 1010 905 1

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 . :)
..
the code below and need updating.

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

Edited by ~s.o.s~: formatting

1
Contributor
1
Reply
2
Views
11 Years
Discussion Span
Last Post by rosy99
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.