By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 3,102 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

update field

P: n/a
Update filed with percentage

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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Isn't multiplying by 1.2 going to increase the price by 20%? Your bank
account will love it. But it might be a little bit of a shock to your
customers :-)
I suggest trying

DoCmd.RunSQL "UPDATE Products SET SalesPrice = SalesPrice * 1.02"

Pavel

Johm wrote:

Update filed with percentage

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

Nov 12 '05 #2

P: n/a
Use an update query like
UPDATE Products SET Products .SalesPrice = [SalesPrice]*1.02;

Make sure you multiply by 1.02. The 1.2 you suggest is a 20% uplift. The
chancellor would not like it

HTH

Phil
"Johm" <ke**@abv.bg> wrote in message
news:24**************************@posting.google.c om...
Update filed with percentage

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

Nov 12 '05 #3

P: n/a
WHERE clauses are used to select the records, not update the value. The SET
clause is used to update the value.

Here's some SQL from a Query created using the QueryBuilder that works on a
table in one of my test databases:

UPDATE tblSomeNums SET tblSomeNums.AFloat = [AFloat]*1.5;

Larry Linson
Microsoft Access MVP
"Johm" <ke**@abv.bg> wrote in message
news:24**************************@posting.google.c om...
Update filed with percentage

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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.