Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

access 2010 storing monetary calculations with 2 decimal places not 4

Posted on 2014-02-02
8
807 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 36

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 36

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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
 
LVL 36

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

829 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