Currency Data - rounding error ?

[dhstein]

I have 3 fields in a table - cost, freight cost and total cost (cost +
freight) - I know I should leave off the total cost and just calculate it -
but I didn't. All 3 fields are defined as currency. On the form that the
user sees they are all formatted as currency. Some items are off by a penny
however, so that if the cost is $2.01 and the freight is $.05 the total
might show as $2.05 instead of $2.06. Is there a rounding or truncation or
some function I could use to store this total value as the correct amount?
Thanks for any help on this.

 

[Douglas J. Steele]

"defined as currency", or simply formatted as currency? In other words, are
the fields declared as Number with a length of Single, Double or Decimal, or
are they declared as Currency? Make sure they're defined as currency.

And yes, you should not be storing the total in the table.

 

[dhstein]

Yes the fields are defined as currency in the table definition - AND in
addition the fields are formatted as currency in the form. Thanks.

 

[Ken Sheridan]

If the costs are themselves computed, e.g. a gross cost computed with NetCost
* (1 + TaxRate) then the values you see may well be rounded from the actual
underlying currency values. The currency data type has a precision to four
decimal places. This is intended to suppress cumulative rounding errors as
usually an operation such as the above would be undertaken at the level of
the aggregated values, i.e. the tax would be computed on the sum of the net
costs, not on the individual item costs.

If this is what's causing the apparent discrepancy and you want to avoid it
then you should add the rounded cost to the rounded freight charge by means
of the Round function. You can see the difference by entering some values in
the debug window, e.g.

? 12.3456 + 4.5678
16.9134
? Round(12.3456 + 4.5678,2)
16.91
? Round(12.3456,2) + Round(4.5678,2)
16.92

Ken Sheridan
Stafford, England