Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Currency Calculation - Financial

Currency Calculation - Financial

Currency Calculation - Financial

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"?

RE: Currency Calculation - Financial

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.

RE: Currency Calculation - Financial


CODE -->

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close