2 decimal place accuracy

J

JohnPapa

Registered User.
Local time
Tomorrow, 01:21
Joined
Aug 15, 2010
Messages
1,036
Access does not have a built-in number representation with 2 decimal places. For example Currency is 4dp etc.

If the result of an operation involving two numbers needs to have an accuracy of 2dp, is there a full-proof method to make sure that the variable is stored with 2dp?

As an example if the variable that needs to have a accuracy of 2dp is called Amount, the following will ensure that it is stored with 2 dp

Amount = Int((Amount+0.005)*100)/100
 
You can use the decimal data type to store the numbers. This allows you to specify the exact decimal places.
 
Josef P. said:
You can use the decimal data type to store the numbers. This allows you to specify the exact decimal places.
Click to expand...
AFAIK Decimal has a precision of 28dp, which is of little use. I need to make sure that a number is stored with 2dp.

As I mentioned, the following will work

Amount = Int((Amount+0.005)*100)/100
 
Maybe you take a closer look at the decimal data type. ;)

Code: 
Const SampleTableDDL As String = _
         "create table DecSampleTab (" & _
         "id long identity(1, 1) primary key," & _
         "DecField decimal(18,2))"

CurrentProject.Connection.Execute SampleTableDDL

Saving with the appropriate rounding is still necessary. However, compared to double/float, an exact value is saved.

/edit:
Read through again: You mean within VBA and not at all later for saving. I was thinking of calculating in VBA and then saving in a table.
=>
You could use long and multiply by 100.
This eliminates a floating point number.
 
Last edited:
Josef P. said:
Maybe you take a closer look at the decimal data type. ;)

Code: 
Const SampleTableDDL As String = _
         "create table DecSampleTab (" & _
         "id long identity(1, 1) primary key," & _
         "DecField decimal(18,2))"

CurrentProject.Connection.Execute SampleTableDDL

Saving with the appropriate rounding is still necessary. However, compared to double/float, an exact value is saved.

/edit:
Read through again: You mean within VBA and not at all later for saving. I was thinking of calculating in VBA and then saving in a table.
Click to expand...
 
Problem with floating point numbers: there are numbers that cannot be represented exactly.
Code: 
Dim x As Double
x = Int(20) / 100
If (x * 100 - 20) <> 0 Then
   Debug.Print x * 100, 20, "but not equal"
End If

76.07 - 67
=>
(7607 - 6700) / 100
or
cdec(76.07) - 67 ... no calculation with floating point numbers
 
Save the number in your table as a Long Integer. Then scale it for presentation.

SQL: 
SELECT YourSavedLongInteger, YourSavedLongInteger / 100 As YourPerfectlyScaled2DPLongInteger
FROM YourTable
 
If you wanted to get fancy you could write a class called cScaledInteger, with code like....
Code: 
Private value_ As Long  ' this is the scaled Long Integer

Public Property Get Value() As Single
    ' return a 2dp number to consumers
    Value = value_ / 100
End Property
Public Property Let Value(Unscaled As Single)
    ' scale to integer when we receive the value
    value_ = Unscaled * 100
End Property

Then, in other code, you can create an instance, and use it very simply...
Code: 
Private Sub MyScaledIntegerTest()
    Dim var
    Dim tmp As New cScaledInteger
    
    ' test a list of values
    For Each var In Array(1.123456, 7.1, 5.9999999, 12.005)
        tmp.Value = var
        Debug.Print tmp.Value
    Next
End Sub
 
You are asking the wrong question, I think. You DON'T CARE that your storage method has more than 2 fractional digits. If you use currency and ONLY fill in two of the four digits after the decimal point, and if you only show the results to two digits using a format statement, you DON'T CARE that internally, you have two digits to play with. You might think... oh, well, certain math operations will affect that. Yes, they will - and would do so in the real world, too. The question is, WHY do you think you need that precision to be exact? 2nd question is, what is the largest number you would expect to have to represent this way? Currency is an integer format that has been typecast to have four decimal places. BUT it never goes beyond four places. 3rd question is, will you have ANY FRACTIONAL multipliers? Will you have ANY divisors? Because in the real world when you compute interest or tax with a fractional base, you get other than two decimal places. So what is the problem you are trying to solve?
 
Formatting does NOT affect how data is stored. So, if you want to ensure that your numbers always total correctly, you MUST round every calculation to two decimal places if you are using single or double. If you are using currency or decimal, you only have to round calculations that include operations other than +-. Otherwise, if you rely on formatting, you can end up summing a dozen numbers and being off a penny or two because the stored data is being summed and therefore those pesky extra decimal places are being summed.


Remember, and this is really important - When you format a column at the table level you are obfuscating the actual value, you are NOT changing what is stored. My position is to NEVER format at the table level for this reason if the format will hide anything. The actual biggest issue is when people format a date field as short date but make the mistake of using Now() to save the value when they should have used Date(). They end up with time as part of the date and can't figure out why their selection criteria isn't working. #5/18/2023 8:30 PM# is NOT between #5/1/2023# and #5/18/2023#
 
Last edited:
Pat Hartman said:
Formatting does NOT affect how data is stored. So, if you want to ensure that your numbers always total correctly, you MUST round every calculation to two decimal places if you are using single or double. If you are using currency or decimal, you only have to round calculations that include operations other than +-. Otherwise, if you rely on formatting, you can end up summing a dozen numbers and being off a penny or two because the stored data is being summed and therefore those pesky extra decimal places are being summed.


Remember, and this is really important - When you format a column at the table level you are obfuscating the actual value, you are NOT changing what is stored. My position is to NEVER format at the table level for this reason if the format will hide anything. The actual biggest issue is when people format a date field as short date but make the mistake of using Now() to save the value when they should have used Date(). They end up with time as part of the date and can't figure out why their selection criteria isn't working. #5/18/2023 8:30 PM# is NOT between #5/1/2023# and #5/18/2023#
Click to expand...
Agree with Pat and that was the problem which I tried to show.

The_Doc_Man: Stating the obvious, if not properly handled

76.07 - 67 = 9.06999999999999

and an if statement like the following

IF 76.07 - 67 = 9.07 then 'will be FALSE

I have been using rounding for the past several decades and when I started this thread I was only wondering whether there was something new on the subject. Apparently there is not. Will look into MarkK's suggestion of creating a class.
 
you can even create a Function in a module:
Code: 
'to decimal value
Public Function ToDec(ByVal p As Double) As Double
    ToDec = CDec(p)
End Function

'addition
Public Function decimalAdd(ByVal p1 As Double, ByVal p2 As Double) As Double
    decimalAdd = CDec(p1) + CDec(p2)
End Function
'subraction
Public Function decimalSub(ByVal p1 As Double, ByVal p2 As Double) As Double
    decimalSub = CDec(p1) - CDec(p2)
End Function
'multiplication
Public Function decimalMul(ByVal p1 As Double, ByVal p2 As Double) As Double
    decimalMul = CDec(p1) * CDec(p2)
End Function
'division
Public Function decimalDiv(ByVal p1 As Double, ByVal p2 As Double) As Double
    decimalDiv = CDec(p1) / CDec(p2)
End Function

debug.print decimalSub(76.07,67)
result: 9.07
 
arnelgp said:
you can even create a Function in a module:
Code: 
'to decimal value
Public Function ToDec(ByVal p As Double) As Double
    ToDec = CDec(p)
End Function

'addition
Public Function decimalAdd(ByVal p1 As Double, ByVal p2 As Double) As Double
    decimalAdd = CDec(p1) + CDec(p2)
End Function
'subraction
Public Function decimalSub(ByVal p1 As Double, ByVal p2 As Double) As Double
    decimalSub = CDec(p1) - CDec(p2)
End Function
'multiplication
Public Function decimalMul(ByVal p1 As Double, ByVal p2 As Double) As Double
    decimalMul = CDec(p1) * CDec(p2)
End Function
'division
Public Function decimalDiv(ByVal p1 As Double, ByVal p2 As Double) As Double
    decimalDiv = CDec(p1) / CDec(p2)
End Function

debug.print decimalSub(76.07,67)
result: 9.07
Click to expand...
Why do you mix Double and Decimal?

I have checked that you get the correct answer. My question is why, since CDec(6.999999) will still give you 6.999999, even when it is stored in a Double.

Dim dblDum As Double
dblDum = CDec(6.999999)
 
it first convert the 2 numbers to "high" precision number before performing the operation.
there must be a better explanation on the net that i can't express.
 
arnelgp said:
it first convert the 2 numbers to "high" precision number before performing the operation.
there must be a better explanation on the net that i can't express.
Click to expand...
:) Many thanks. Will search. In the mean time my approach is clear and works,
Amount = Int((Amount+0.005)*100)/100
 
Just as a slight side step - the currency data type doesn't give you the rounding errors though, as it is really an integer value that is shifted internally four places to give the decimal value.

Code: 
Sub Flibble()
    
    Dim dTest As Currency
    
    dTest = 76.07 - 67
    Debug.Print dTest
    
End Sub

In the immediate window
flibble
9.07

It is very frustrating that you can't declare a variable as a Decimal data type with a fixed precision, it would save a lot of faffing about.
 
Minty said:
Just as a slight side step - the currency data type doesn't give you the rounding errors though, as it is really an integer value that is shifted internally four places to give the decimal value.

Code: 
Sub Flibble()
  
    Dim dTest As Currency
  
    dTest = 76.07 - 67
    Debug.Print dTest
  
End Sub

In the immediate window
flibble
9.07

It is very frustrating that you can't declare a variable as a Decimal data type with a fixed precision, it would save a lot of faffing about.
Click to expand...
If anyone is interested I did some tests as indicated below,
Double is not OK
Single, Currency are OK


Code: 
Dim Dum1 As Double
Dim Dum2 As Double
Dim Dum3 As Double


Dum1 = 76.07
Dum2 = 67


Dum1 = Int((Dum1 + 0.005) * 100) / 100
Dum2 = Int((Dum2 + 0.005) * 100) / 100
Dum3 = Int(((Dum1 - Dum2) + 0.005) * 100) / 100
' Dum3 = 9.07




Dim Dum1c As Currency
Dim Dum2c As Currency
Dim Dum3c As Currency


Dum1c = 76.07
Dum2c = 67
Dum3c = Dum1c - Dum2c
' Dum3c = 9.07


Dim Dum1d As Double
Dim Dum2d As Double
Dim Dum3d As Double


Dum1d = 76.07
Dum2d = 67
Dum3d = Dum1d - Dum2d
' Dum3d = 9.699999999 etc


Dim Dum1s As Single
Dim Dum2s As Single
Dim Dum3s As Single


Dum1s = 76.07
Dum2s = 67
Dum3s = Dum1s - Dum2s
' Dum3s = 9.07
 
Minty said:
Just as a slight side step - the currency data type doesn't give you the rounding errors though, as it is really an integer value that is shifted internally four places to give the decimal value.

Code: 
Sub Flibble()
   
    Dim dTest As Currency
   
    dTest = 76.07 - 67
    Debug.Print dTest
   
End Sub

In the immediate window
flibble
9.07

It is very frustrating that you can't declare a variable as a Decimal data type with a fixed precision, it would save a lot of faffing about.
Click to expand...
The only problem I can see with a Currency field is for the 3rd or 4th digit to alter the first two digits.
 
You must log in or register to reply here.

Similar threads

A
Access Audittrail
Replies
5
Views
250
CJ_London
C
G
Difference in value of entered data and sum
Replies
4
Views
861
Pat Hartman
P
A
Solved How to hide decimal zeros and cells with zero value and show whole numbers.
Replies
5
Views
1,757
Ajabu
A
Uncle Gizmo
Finding Inspiration for MS Access Blogs
Replies
0
Views
272
Uncle Gizmo
Uncle Gizmo
PaquettePaul
Solved Weird VBA in RunTime
Replies
5
Views
596
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom