Solved

access 2010 storing monetary calculations with 2 decimal places not 4

Posted on 2014-02-02
8
762 Views
Last Modified: 2014-02-03
Hi,

I need advice on the best way to calculate monetary calculations and store them in a table for later retrieval.

At present I am calculating various sums using the CCur function. A basic example is below:
iNewIPT = CCur(Me.txtNewPrem * 0.06)

I am storing this sum in a table field "IPT" where the Data Type is "Currency", the Format is "Standard"**, decimal places set to "Auto".

However, when i display the results in a form sometimes the values shown have 4 decimal places (depending on the calculation), even if i set the formatting to Currency, 2 decimal places

The possible solutions as i can see are:
a) Use a custom function to calculate all sums in VBA (which i do not have)
b) set the decimal places in the table to 2
c) a combination of both

I do not wish to use Bankers rounding as the are UK premium calculations, so exact rounding up/down is required.

Many thanks

** The reason i wish the format to be standard is that sometimes the results will be calculated in £GBP and sometimes in Euros.

I use a function on each form to set the currency to be displayed as follows:
Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            If ctl.Tag = "SetCurrency" Then
                If Myform!Currency = "EURO" Then
                    ctl.Format = "Euro"
                Else
                    ctl.Format = "Currency"
                End If
            End If
        End If
    Next ctl
0
Comment
Question by:andrewpiconnect
  • 3
  • 3
  • 2
8 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39828153
The Currency data type is not the same as the Currency format.  The Currency data type is a scaled integer with a fixed four decimal places.  The problem is setting the decimal places to auto.  The currency data type by definition uses four decimal digits so auto tells it to show as many as it needs to.  If you use the Currency format, that shows only two decimal places since that is how currency is displayed.  If you use the Standard format, you must specify 2 as the decimal places if that is all you want to show.
0
 

Author Comment

by:andrewpiconnect
ID: 39828196
Ok Pat,

But if i set the Auto decimal places to 2, will this use the access round function and round up/down numbers with unpredictable results?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39828274
Rounding isn't unpredictable.  There are rules.  I don't have them handy but you can probably find them if you look.  There are rules where 5 is always rounded up or others where 5 is rounded up or down depending on the digit to its left.

The calculation is rounded to 4 decimal places  for storage and then rounded for display to 2.  Internal calculations will still use the 4 decimal places since that is what is saved in the table regardless of what you say regarding decimal places on the table definition.  Remember, the Data Type dictates how the data is stored and Currency specifies 4 decimal places.  The other properties are used to specify how to display it.
0
 

Author Comment

by:andrewpiconnect
ID: 39828284
Ok, all is getting clearer now. Thank you.
Just one query tho. Is it possible to stipulate the rule that always rounds up a 5 regardless of what number is to tbe left of it? Or does the currency format handle that correctly by itself?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:PatHartman
ID: 39828649
I'm pretty sure that is the default rule.  It's easy enough to test if you can't find the rule.  Do the calculation in the debug window.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39828903
All native rounding in Access (VBA) is Banker's Rounding including the rounding performed by the CCur, CDbl, etc. functions.

Except one, Format, which even is quite easy to handle.
So, for your case, as Format returns a string:

iNewIPT = CCur(Format(Me!txtNewPrem * 0.06, "0.00"))

Data type Currency always holds four decimals, but this way values will always be a.bc00.
As mentioned, for display you can set the decimal count to two.

/gustav
0
 

Author Closing Comment

by:andrewpiconnect
ID: 39829780
Excellent....nice and simple, although i had to go through each module and apply the format code to each calculation. all done now....many thanks
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39829813
You are welcome!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now