format result of an update query

  • Thread starter Thread starter Helen
  • Start date Start date
H

Helen

Hi,

I have created an update query against a table using the following in the
Update field:

dbo_INVPRICE!SellingPrice+(dbo_INVPRICE!SellingPrice*0.035)

Currently the result will have 5 decimals, and I need to ensure it only has
2. I tried using Properties > Format for the field, but that didn't do it.
Is there something that I can add to my Update field to make sure it only
uses 2 decimals?

Many thanks,

Helen
 
Dear Helen:

I would think this is not technically a "formatting" problem, as an update
does not specify format.

Rather, this looks like a values problem. If you wish to add 3.5% to the
values in the SellingPrice column, you would round off the value in the
calculation where you updated it.

Assuming you have already updated the table and it is too late to fix it
(you might want to save a copy of the database before performing potentially
destructive action queries) you can update it again just to round the values
that are there.

While formatting can be used to make this appear to be fixed, it is really a
bad idea.

Consider what happens when you have two values 1.126 and 2.356. When
formatted to appear as two decimal places, these will be shown as 1.13 and
2.36. When added together they will always be 3.482. If this sum is
formatted to 2 decimal places, it will appear that 1.13 + 2.36 = 3.48.
That's not such a good thing. That's why the concept needs to be that the
values are wrong, not that the formatting is wrong.

Is that clear?

Tom Ellison
 
You're absolutely right. :-)

So, how would I go about and do the Rounding then? I have not done the
update yet.

Thanks!

Helen
 
Back
Top