Currency Data Type 2 decimals - actual value shows 4 decimals

[Guest]

I have a field that is data type currency, format is currency, digits is 2
but the actual value when performing calculations stores a value with 4
digits after the decimal even thought I ensure that all field properties for
every reference or interaction with the field is decimals 2. Should I
change the field type to number and then just format the field when creating
a report?

 

[TC]

Currency fields do all their calculations to 4 decimal places, for the
specific purpose of ensuring that rounding errors do not accumulate &
eventually lead to big errors.

Be thankful that Access is doing that for your currency values! Just
truncate or round their values to 2 places, when you want to print them
out, or display on the screen. Yu can do that with the Format$()
function, check it out in Access help.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Wayne Morgan]

No, don't change the field type. The Currency data type handles decimal
calculations much better than Single or Double do. The Currency data type
does have 4 decimal places. Setting the number of decimals to 2 in the field
changes the display of the field, not the actual value of the field. If you
only want 2 decimals stored, you'll need to round-off your results to 2
decimals before you store them in the table. There will still be 4 decimals
stored, but the last two will be 00.

 

[Guest]

Thanks for the response - would you be so kind to help me understand how to
round off the field. I don't know how to do this.
Warm Regards,
Cindy

 

[Wayne Morgan]

It will depend on where you're doing the calculations, but basically to
round to 2 decimal places, do one more calculation on your result.

curResult = Int(curResult * 100 + 0.5) / 100

This will round to 2 decimals with .5 always rounding up. If you prefer
"scientific" or "bankers" rounding where .5 always rounds to an even number,
you could use the built-in Round() function.

curResult = Round(curResult, 2)

 

[Guest]

Thank you sincerely for your help.

It will depend on where you're doing the calculations, but basically to
round to 2 decimal places, do one more calculation on your result.

curResult = Int(curResult * 100 + 0.5) / 100

This will round to 2 decimals with .5 always rounding up. If you prefer
"scientific" or "bankers" rounding where .5 always rounds to an even number,
you could use the built-in Round() function.

curResult = Round(curResult, 2)