update field

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

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:[color=blue]
>
> 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[/color]

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" <keks@abv.bg> wrote in message
news:2402261c.0404210812.4f2f4aeb@posting.google.c om...[color=blue]
> 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[/color]

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" <keks@abv.bg> wrote in message
news:2402261c.0404210812.4f2f4aeb@posting.google.c om...[color=blue]
> 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[/color]