Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2006
    Posts
    18

    Question Unanswered: Need help please (merged multiple threads)

    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     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 .

    ..
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2006
    Posts
    18

    Question 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

    Code:
    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 .

    ..
    Attached Files Attached Files

  3. #3
    Join Date
    Aug 2006
    Location
    Dewsbury, UK
    Posts
    9

    Question

    But you have two contradictory statements in what you want to do...??

    Quote Originally Posted by sarab99
    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"
    ..

  4. #4
    Join Date
    Aug 2006
    Posts
    18
    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.

    the code is as blewo but its need some correction.

    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



    hope this help
    Last edited by sarab99; 08-23-06 at 16:26.

  5. #5
    Join Date
    Aug 2006
    Location
    Dewsbury, UK
    Posts
    9

    Wink

    Hey Sara,

    I think the following should do what you want:
    Code:
    'since we havent started, indicate that prev price hasn't been used
    prevprice = -10
    'Do the following until the end of the recordset
    Do while not rst.EOF
    'Grab the code (I'm assuming you use this later)
    	Code = rst("code")
    'Grab the price
    	Price = rst("price")
    'check that a previous price has been set
    '(should be greater than the pre-set one)
    	if prevprice > -10 then
    'if the new price is greater than the old one, then set res to 1
    'otherwise set res to 0
    		if price>prevprice then 
    			res = 1
    		else
    			res=0
    		end if
    	end if
    'set the current price to prevprice ready for the next record
    	prevprice = price
    'move to the next record in the recordset
    	move next
    'loop back to the do while
    loop
    I hope that helps, I've commented it so that you can see what I've done without it interfering with your code.

    Have fun,

    5p0ng3b0b

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello sarab!

    Here it is, look at Query1, Form1, Module1.
    Open Form1 and try. I think it is what you need.
    Attached Files Attached Files

  7. #7
    Join Date
    Aug 2006
    Posts
    18
    thanks MStef-ZG

    your do good job. but the code need to be care with the "code" fileld as this field is variable. like 1010,1020, 1030 ect.

    so the vb code must check the code number and Id to generate the result.

    I have attaced the db with full data. to chek.

    thank you for assistant.
    Attached Files Attached Files

  8. #8
    Join Date
    Aug 2006
    Posts
    18
    its gave error in line prevprice = -10

    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
    Last edited by sarab99; 08-24-06 at 12:20.

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    what was the error?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Aug 2006
    Posts
    18
    more clarification:

    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 buy 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

  11. #11
    Join Date
    Aug 2006
    Posts
    18
    more clarification:

    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 buy 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

  12. #12
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    what was the error?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  13. #13
    Join Date
    Aug 2006
    Posts
    18
    the error is debog the line in yellow color.

    note I am not expert on this, some one prepared the obove vb code. but now I missed him.

  14. #14
    Join Date
    Aug 2006
    Location
    Dewsbury, UK
    Posts
    9
    http://www.moorhouses.plus.com/VBA%2...02000(new).zip

    all done and dusted - I took the liberty of creating a macro to allow you to run it easily. It doesn't appear to do anything, but this is just because it's so fast. I didnt add msgbox "Complete" or anything incase you were automating it into a bigger db.

    All the best,

    5p0ng3b0b

  15. #15
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    OK, I think it will be OK now. I put a PRIMARY KEY on fields CODE and ID.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •