Results 1 to 4 of 4

Thread: SQL statement

  1. #1
    Join Date
    Feb 2004
    Location
    Southeast
    Posts
    2

    Unanswered: SQL statement

    strsql = "UPDATE Shipping SET Shipping.Qty = Qty - 1 " _
    & "WHERE (((Shipping.Qty)>0) " _
    & "AND ((Shipping.PartNo)= '" & Forms![Axle]![Axle No] & "'"
    & "))" _
    & ""

    dbs.Execute strsql

    The above is VB code in an Access2000 application. I need it to update the first record that matches the criteria and no others. It currently updates each record in the Shipping table that matches the Axle No from the form.

  2. #2
    Join Date
    Feb 2004
    Location
    New Jersey
    Posts
    17

    Update statement

    Hi
    Your current statement is "Update tblname set cond1='cond1'
    Change it to "Update tblname set cond1=(select top1 from tblname where cond1='cond1)".
    Let me know if this works.Good luck.
    Cinil

  3. #3
    Join Date
    Feb 2004
    Location
    Southeast
    Posts
    2

    Re: Update statement

    Originally posted by Cinil
    Hi
    Your current statement is "Update tblname set cond1='cond1'
    Change it to "Update tblname set cond1=(select top1 from tblname where cond1='cond1)".
    Let me know if this works.Good luck.
    Cinil
    Cinil,
    Thank you for your response. I am still having trouble with the syntax of the SQL statement. I am not very familiar with SQL code and reqire a bit more explicit direction.
    Tony

  4. #4
    Join Date
    Feb 2004
    Location
    New Jersey
    Posts
    17

    Re: SQL statement

    Hi Tony,
    I regret my first reply bcos it doesnt hold true always.The answer to your question is NO unless you change your WHERE clause.Else it will update all the rows.Try to find out some unique ids which you can include in your WHERE clause.The other way,but little more complicated, is to use cursors and exit from the loop after the first row.Having fun with sql...?
    Cinil


    Originally posted by Tony McCluney
    strsql = "UPDATE Shipping SET Shipping.Qty = Qty - 1 " _
    & "WHERE (((Shipping.Qty)>0) " _
    & "AND ((Shipping.PartNo)= '" & Forms![Axle]![Axle No] & "'"
    & "))" _
    & ""

    dbs.Execute strsql

    The above is VB code in an Access2000 application. I need it to update the first record that matches the criteria and no others. It currently updates each record in the Shipping table that matches the Axle No from the form.

Posting Permissions

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