Rounding currency UP not down

[Nancy Lytle]

I have a table where some data has been entered incorrectly, leaving us with
data such as unit cost 69.985, when it should be 69.99 or 49.985 when it
should be 49.99 (these are currency fields).

Since there are several hundred thousand records, and an unknown number have
this "quirk", I'd like to be able to run an update query that could catch
any of the bad amounts and "round them down". Using Access functions it
always seems to round down to say, 69.98 not 69.99.

Does anyone have any ideas as to how I can handle the current bad data? I
am investigating the offending code but need to run a batch of records
before I can get the code fix done.

Thanks in advance.
Nancy Lytle

 

[Tom Ellison]

You could use an standard rounding function (5 up/4 down) but add .005 to
the value first. This will do what I think you are saying you want.

Before you update the table, you should write this as a SELECT query first
to make sure it is doing exactly what you want. Something like:

SELECT YourColumn, ROUND(YourColumn + 0.005, 2) FROM YourTable WHERE
YourColumn <> ROUND(YourColumn + 0.005, 2)

Look at these results to see what it's doing with the values. Then, back up
the database before running an update. Once you update, you won't be able
to go back if there's some error unless you have a backup. VERY IMPORTANT!

Tom Ellison

 

[Nancy Lytle]

Thank you for your response, it has headed me in the right direction.

Nancy