?
Solved

access 2010 storing monetary calculations with 2 decimal places not 4

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

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 39

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 39

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 51

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 51

Expert Comment

by:Gustav Brock
ID: 39829813
You are welcome!

/gustav
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

718 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