Currency Calculation - Financial

[Goondu]

I have a table for a percentage in Tax.

Data Type = Number
Field Size = Single

Another table with Data Type set to Currency. Field = Amount.

So I have a report to calculate the Tax.

txtTax = 0.05
txtAmount = 16998650

Text1 = txtAmount * txtTax

Result of Text1: $16,998,650.51

We have a rounding error of 0.01

So, if I were to use the Field Size of Double or Decimal. There's no problem in rounding. However, there are problems with floating point errors that might happen or occur with these Data Types.

So the question is, what Data Type do we use for Tax?

I'm thinking of

Choice 1
Data Type = Number
Field Size = Decimal

Choice 2
Data Type = Currency
Format = Currency
Decimal place = Auto

The percentage of Tax is a decimal number, but with Single the rounding error will occur in some cases. If Decimal, it works but doesn't look good with floating points.

So the best choice seems like "Choice 2"?

 

[lameid]

I would say that in general you want to record whole cents since that is what you are collecting and remitting. I think everyone expects rounding. Generally speaking you are in the clear as long as you reasonably collect accurate tax and you remit what you collect.

That said, your example is broken.

You may want a great deal of precision before getting to dollars, it really depends on your process.

Something I stumbled upon is that if you are working with long running operations you may benefit to make a string with the arithmetic expression instead and keep concatenating to it. At the end pass it to the Eval function. I had to do this to remove FP error from dividing by small decimals repeatedly.

 

[Goondu]

txtTax = 0.07
 txtAmount = 16,998,650

 Text1 = txtAmount * txtTax

 Result of Text1: $1,189,905.51

 We have a rounding error of 0.01

Sorry about that. The Tax value should be 0.07. (Format = General Number, since the $ sign looks odd)

The standard value would be $1,189,905.50.

So, in this case we have a unwanted 0.01 roundup when we use "Single" for Tax percentage.

What I want to know is "Is it safe to use Choice 2?, since it would be the Access way to dealing with currencies.