how to store numbers - payroll application

[cglisc]

Howdy,

Whats is the best way to handle number (currecy) storage?
Access stores Currency values with 4 digis precision.
So if you have 4 currency fields and you do a sum of
them using SQL, you will not neccessarily get the
expected result.

The way I see it, there's 2 ways:
1. Round the number to 2 decimal points before
sending it to the database. Would anyone advice against this?

2. Store the number as is, and round it before showing it
on a form or report. This solution sounds very inefficient
to me.

Any advice would be greatly appreciated.

Chris

 

[substitute]

Currency data type is optimised for currency data, hence only 4 decimal digits. This avoids strange rounding errors so if you store 25 cents you will get back 25 cents.

If you need more digits for, say, currency conversion, use Number/Double, but you will get some strange effects as there are about 10 decimal places. 25c might turn out to be $0.2500000000124.

The sure way to do it is to express all values in cents in Number/Long fields and divide by 100 before displaying.

 

[BillPower]

Say if you were mutiplying, before storing in my DB I would use:

Me!TotalCost = CCur(CLng(Me!Cost * Me!Quantity * 100)) / 100

Your addition will always be spot on and reflect whatever is shown on screen or in a report.

Bill