Results 1 to 2 of 2
2004-04-21, 16:04 #1
- 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
2004-04-21, 16:11 #2
- Join Date
- Mar 2002
- 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.)