update field

[Johm]

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

[Pavel Romashkin]

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

[Phil Stanton]

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

[Larry Linson]

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