Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

access 2010 storing monetary calculations with 2 decimal places not 4

Posted on 2014-02-02
8
Medium Priority
?
980 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 40

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 40

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 40

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 52

Accepted Solution

by:
Gustav Brock earned 2000 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 52

Expert Comment

by:Gustav Brock
ID: 39829813
You are welcome!

/gustav
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

916 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