Solved

access 2010 storing monetary calculations with 2 decimal places not 4

Posted on 2014-02-02
8
835 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
[X]
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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 39829813
You are welcome!

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

726 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