Rounding

[brbarto]

I was doing this calculation =[Gallons][UnitAmt] and was
having trouble with it not rounding correctly. UnitAmt is
Currency; Gallons is Double(fixed, 1 decimal place). To
correct the problem, I think, I changed it to =CCur(NZ
([Gallons]*[UnitAmt])). I don't really understand why CCur
is correcting the problem. I used NZ because I was getting
#Error in the total field of the next record. I am using
Access97, but this is actually going to be used in 2000.
Is this the correct way to handle all of this??

Thanks

 

[JoaoTL]

Sub DoubleVsDecimal()
' This procedure demonstrates how using the
' Decimal data type can minimize rounding errors.

Dim dblNum As Double
Dim varNum As Variant
Dim lngCount As Long

' Increment values in loop.
For lngCount = 1 To 100000
dblNum = dblNum + 0.00001
' Convert value to Decimal using CDec.
varNum = varNum + CDec(0.00001)
Next

Debug.Print "Result using Double: " & dblNum
Debug.Print "Result using Decimal: " & varNum
End Sub Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[JimHorton]

brbarto,
The currency would correct it because it's not a floating point type, its an 8-byte integer, the Decimal variable is also an integer, I think 10 or 12 byte. One way to correct this is to store the values in some integer format, such as currency or Long, and scale accordingly. Currency scales by 10000, ie it stores $123.45 as 1,234,500, and any calcs will be done without the floating point processor, which is what results in the precision differences that confound us all the time. Any precision will only go out to the 'virtual' 4 decimal places, and there will never be any discrepancies. So if you store in Long (assuming the values never get larger than your scale factor allows for Long) you can always format the display accordingly and be comfortable in your calculations.
--Jim

 

[Aivars]

Hi!

Here is function for rounding which I wrote during I was worked in Access97:

Function RoundAL(dblValue As Double, Optional lngDecNo) As Double
'Returns a number rounded to a specified number of decimal places
'like Round function in Access 2000, MS Excel etc.
Dim dblVal
Dim lngDecCoeffic
Dim intNeg As Integer

intNeg = IIf(dblValue > 0, 1, -1) 'Exponentiation
If IsMissing(lngDecNo) Then
lngDecCoeffic = 1
Else
lngDecCoeffic = 10 ^ lngDecNo
End If

dblVal = Abs(dblValue) * lngDecCoeffic + 0.5
RoundAL = Int(dblVal) / lngDecCoeffic * intNeg

End Function

Simple copy this function in any module (separate) and use it each time when it's needed (You can use this function in queries, too).

?RoundAL(34.67834,2)
34.68

Aivars