need help in VB Code

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

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

Attached Files

VBA Access 2000.zip (15.0 KB, 52 views)

re: need help in VB Code

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.

re: need help in VB Code

Are you wanting to compare the new price to the most recent previous price?

you have several entries to the same item. The most recent is 904, now I update it to 905. Are these two entries the ones you are concerned with or do you want to compare each entry and show a trend?

If you are just wanting to compare the top 2 entries to each other then use your SQL statement to select the top 2 by date. Why go through all of them.

re: need help in VB Code

I have filed called "code" this code is varialble. could be 1010,1020 or 1030 etc.

this code belong to share stock number. if price is less than previous this mean some one has sold the share , if the price is higher this mean some one has bought the share. the goole of this program to show me how many share has sold and bought.

hope this give you more clarify for my idea.

thank you sir.