access 2010 storing monetary calculations with 2 decimal places not 4
Posted on 2014-02-02
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.
** 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"
ctl.Format = "Currency"