Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update field with percentage (Access 2000)

    In the table Products we have to update the field SalesPrice with 2 percents . We have to increase all the Sales Prices of our products with 2 percent.Our idea is to multiply the price with 1.2 for all the products. However, I get the message " Syntax error in update statement.
    Could someone help me ?
    I tried to use the following function:
    Public Function IncreaseSalesPricewith2Percent()
    Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "UPDATE Products WHERE SalesPrice = SalesPrice * 1.2"
    Set db = Nothing
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update field with percentage (Access 2000)

    In the first place, multiplying by 1.2 will add 20 percent, not 2 percent. You should use 1.02 instead.
    In the second place, you must use SET in an update query:

    db.Execute "UPDATE Products SET SalesPrice = SalesPrice * 1.02"

    Note: it is a good idea to start by creating the query interactively. When it works as intended, switch to SQL view. You will see the correct SQL syntax. That is often easier than trying to write the SQL directly (unless you are very experienced.)

Posting Permissions

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