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 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 4 Years Ago by pyTony: fixed formatting