MS Access: Considerations to handle monetary fields

I am new to MS Access and to monetary fields and I am wondering if there are any special considerations that I have to pay attention to when design and program such fields?

Some initial questions in my head for the database that has multiple currencies.

What is the best data type for the fields that has monetary data?

Is it Number or currency? and what should be the variable type in vba that can hold such field data?

Initially, I made two separate fields on as a Number and the other one as a Number too that should be related to currency table.

According to Currency field in the form the monetary fields will be formatted.

Is this OK?

Would the calculations be correct? because I hear that monetary fields should have some special considerations to keep the money calculation correct  100% ?

Any other considerations I did not mention?

Thanks in advance.
Shadi SalehAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Is it Number or currency? and what should be the variable type in vba that can hold such field data?
you can use:

Dim x As Currency

Open in new window


Would the calculations be correct? because I hear that monetary fields should have some special considerations to keep the money calculation correct  100% ?
A field with data type: Currency should be good enough to handle your case. It was designed for this purpose to handle monetary data.

it seems that you got concern with currency types, for me, i would simply add another field to store the Currency Type.
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
The downside to using currency in access, is that it will then format the number as the currency in your regional settings.
If you database is 100% local, and you only work in a single currency, this is great and simple.

But for your example it can be very confusing. I believe you can override the default formating for each textbox, but it can be a chore remembering it. So currency is fine for VBA (because it doesn't format there), but other than that I prefer to use Decimal(18,4), and store the currency in a seperate field.
Gustav BrockCIOCommented:
You should always use Currency when storing amounts. The actual currency (Euro, Dollar, whatever) for the values must be stored in another field.

The values will by default be displayed using the current local currency, but that is just a default format.
As you will have several currencies, you will anyway have to apply custom formatting whenever you display the values.

/gustav
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Would the calculations be correct? because I hear that monetary fields should have some special considerations to keep the money calculation correct  100% ?>>

 The thing to be aware of with Currency (and the reason you should use it), is that it is a scaled integer and is accurate up to four decimal places.

 If you use a number such as a single or double, decimal accuracy is not guaranteed because they use floating point math.    The currency data type does as well, but it is scaled, which means it is multiplied/divided as you use it.   For example, you want to store:

   1.2345

that will be store internally as:

  12345.

 It has been scaled up to a larger number to ensure that the value for four decimal positions fall into the integer portion of the number.     When you go to use it, it will be divided by 1000 (scaled down) and you will see 1.2345.   This is done automatically for you.

You could also use the decimal data type (another scaled integer, but you get to specify the scale), but most find a currency data type to be easier.  As mentioned, currency fields are formatted automatically for you, but you can remove the formatting.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.