2 decimal place accuracy (1 Viewer)

[JohnPapa]

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

 

[Josef P.]

You can use the decimal data type to store the numbers. This allows you to specify the exact decimal places.

 

[JohnPapa]

You can use the decimal data type to store the numbers. This allows you to specify the exact decimal places.

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

 

[Josef P.]

Maybe you take a closer look at the decimal data type.

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.

 

[JohnPapa]

Maybe you take a closer look at the decimal data type.

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.

 

[JohnPapa]

Basically, if you have any operation with two numbers, decimals included, the result may not be exactly what you expect. As an example please look at
(1) Question - Huge decimal places when subtracting two numbers | Access World Forums (access-programmers.co.uk)

76.07 - 67 = 9.06999999999999

If you treat the numbers in the way I suggested you will get 9.07

 

[Josef P.]

Problem with floating point numbers: there are numbers that cannot be represented exactly.

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

 

[NauticalGent]

Mike Wolfe has a few articles on his site that you may find of use...

 

[MarkK]

Save the number in your table as a Long Integer. Then scale it for presentation.

SELECT YourSavedLongInteger, YourSavedLongInteger / 100 As YourPerfectlyScaled2DPLongInteger
FROM YourTable

 

[MarkK]

If you wanted to get fancy you could write a class called cScaledInteger, with code like....

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...

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

 

[The_Doc_Man]

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?

 

[Pat Hartman]

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#

 

[JohnPapa]

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#

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.

 

[arnelgp]

you can even create a Function in a module:

'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

 

[JohnPapa]

you can even create a Function in a module:

'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

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)

 

[arnelgp]

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.

 

[JohnPapa]

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.

Many thanks. Will search. In the mean time my approach is clear and works,
Amount = Int((Amount+0.005)*100)/100

 

[Minty]

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.

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.

 

[JohnPapa]

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.

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.

If anyone is interested I did some tests as indicated below,
Double is not OK
Single, Currency are OK

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

 

[JohnPapa]

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.

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.

The only problem I can see with a Currency field is for the 3rd or 4th digit to alter the first two digits.