Faulty Currency Calculation

  • Thread starter Thread starter Ian D.
  • Start date Start date
I

Ian D.

I need some help with a Currency Calculation that seems straitforward.
Sub Total 19.95
GST 1.40 I get this by calulating SubTotal*.07
ProvTax 1.60 I get this by calulating SubTotal*.08
TOTAL 22.94 I get this by adding the 3 fields.

All 3 fields are Set as Currency, with Decimal.property set as Auto
When I click in iether of the GST or ProvTax or TOTAL fields
4 decimal places are shown. GST shows 1.3965 ProvTax shows 1.596
hence the seemingly faulty Calculation of 22.94 instead of 22.95.
Even if I set the Decimal property to 2, I still get the same calculation.
19.95+1.40+1.60 should equal 22.95 but it always shows as 22.94
WHY & how do I correct for this.

Thanks in advance for any advice.
 
Ian,

The figures are correct, and Currency data type is always to 4 decimal
places. If you want the total to show 22.95 you can use the Round
function, i.e. work out your Total like this:
=[SubTotal]+Round([GST],2)+Round([ProvTax],2)
 
Thanks Steve,

Your solution works perfectly.... Thanks so much!

However, just as a point of frustration....
the help file tells me in two different locations that
a currency field has only 2 decimal places.
One of them says that it gets it decimal setting from the
regional settings of the machine, which happens to be set
to 2 decimal places.
Oh well... I guess MS Access Help is wrong again.
The help file has led me astray on many instances in the past.
Why should it be any different now.

Anyway Thanks very much for your help!
Hope all is as well as can be.


Steve Schapel said:
Ian,

The figures are correct, and Currency data type is always to 4 decimal
places. If you want the total to show 22.95 you can use the Round
function, i.e. work out your Total like this:
=[SubTotal]+Round([GST],2)+Round([ProvTax],2)

--
Steve Schapel, Microsoft Access MVP

I need some help with a Currency Calculation that seems straitforward.
Sub Total 19.95
GST 1.40 I get this by calulating SubTotal*.07
ProvTax 1.60 I get this by calulating SubTotal*.08
TOTAL 22.94 I get this by adding the 3 fields.

All 3 fields are Set as Currency, with Decimal.property set as Auto
When I click in iether of the GST or ProvTax or TOTAL fields
4 decimal places are shown. GST shows 1.3965 ProvTax shows 1.596
hence the seemingly faulty Calculation of 22.94 instead of 22.95.
Even if I set the Decimal property to 2, I still get the same calculation.
19.95+1.40+1.60 should equal 22.95 but it always shows as 22.94
WHY & how do I correct for this.

Thanks in advance for any advice.
Click to expand...
Click to expand...
 
Ian,

I understand your frustration with Access Help, although most complaints
relate to the difficulty of finding the information, rather than with
the accuracy of the information itself, which I generally consider to be
of a high standard. I am unable to find anything that says "a currency
field has only 2 decimal places", and if you can point me to where you
saw this, I will see if someone can look into it. This is what I found
relating to the currency data type in Access 97:
"Currency variables are stored as 64-bit (8-byte) numbers in an integer
format, scaled by 10,000 to give a fixed-point number with 15 digits to
the left of the decimal point and 4 digits to the right. This
representation provides a range of -922,337,203,685,477.5808 to
922,337,203,685,477.5807"
.... and for Access 2003":
"Currency values and numeric data used in mathematical calculations
involving data with one to four decimal places. Accurate to 15 digits on
the left side of the decimal separator and to 4 digits on the right side."

Don't confuse the value of the data with the question of formatting,
which only relates to the way the data is displayed, for example on a
form or report.
 
Back
Top