Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Burgas, Bulgaria
    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
    Thanked 31 Times in 31 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