need help in vb code

pts.
Tags:
Database
DB2
Oracle
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 [CODE] id Time Code Price Res 1 10:00:00 AM 1010 905 [COLOR="Blue"]0[/COLOR] 464 10:00:34 AM 1010 906 [COLOR="blue"]1[/COLOR] 626 10:00:56 AM 1010 906 [COLOR="blue"]1[/COLOR] 1523 10:02:16 AM 1010 904 [COLOR="blue"]0[/COLOR] 1568 10:02:21 AM 1010 904 [COLOR="blue"]0[/COLOR] 1569 10:02:21 AM 1010 905 [COLOR="blue"]1[/COLOR] [/CODE] 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 vb code as below: 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

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could make this work right in the SQL statement:

select
id,
@res:=
case
when price > @previous then 1
when price < @previous then 0
else if(@res is not NULL,@res,0 )
end as res,
@previous as last_price,
@previous:=price as this_price
from tt;

– end sql

Produces:
id res last_price this_price
1 1 905 905
464 1 905 906
626 1 906 906
1523 0 906 904
1568 0 904 904
1569 1 904 905

The SQL should be compatible with most db servers, but I tested with MySQL. I purposely did not put the sql into a vb quoted string so it was easier to follow.

If you prefer to do on the client side within VB:

[code]

……

res=0
previous=0
do while not rst.eof
if rst![price] > previous then
res = 1
elseif rst![price] < previous then
res = 0
‘ else
‘ ”price is the same, do not change res
end if
previous = rst![price]
…..
rst.movenext
loop

[/code]

I didn’t actually run the VB, but you get the idea.

Hope this helps!

Mike

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rosy99
    Iam sorry jggtz 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. could you please download the db and check where is the error and updated http://users.cjb.net/hotelphone/vba%20access%202000.zip thank you
    0 pointsBadges:
    report
  • DaveInAZ
    Hi Rosy99, First, you seem to misunderstand the purpose of this site. We're not here to do your work for you, we're just here to help you do your own work by clarifying issues. Second, your example results show that you're getting exactly what the rules you gave us should produce. Are these actual results, or just a mockup showing what you want? Third, the confusion probably lies in the fact that you want two different results for the same comparison. You need some way of deciding which result you want if the current record is the same as the previous one. Otherwise, you will get inconsistent results. You may have that but, if you do, you didn't tell us. Good luck.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following