A2003 round() not working

[bulrush]

I'm using the internal round() function in A2003, but it is not working. My number I am rounding to 2 decimal places is 10.325. This should round to 10.33, but Access is rounding it to 10.32.

I also found an external function on the internet which has the same error. Here is the function:

Function RoundIt(ByVal Number As Variant, ByVal Decimals As Integer) As Variant

    If Decimals >= 0 Then
        RoundIt = Int(Number * 10 ^ Decimals + 0.5) / 10 ^ Decimals
    Else
        MsgBox "Invalid decimal places."
        RoundIt = Number
    End If

End Function

How can I get Access to round properly? This is a very serious problem to be a penny off. When you are talking about a million calculations, those million pennies really add up.

EDIT:
The Number that is passed to this function is actually of type Single. Here is my broken down code so I could trace what is going on:

Public Function Roundcr(ByVal Number As Variant, ByVal Decimals As Integer) As Variant
Dim v As Variant
' At this point, Number=10.324999999999 but displays as 10.325. Thus begins my problem.
If Decimals >= 0 Then
    v = (Number * (10 ^ Decimals)) ' v=1032.4999999
    v = v + 0.5 ' v=1032.9999999
    v = Int(v) ' v=1032
    v = v / (10 ^ Decimals) ' v=10.32 which is NOT CORRECT.
    Roundcr = v
Else
    MsgBox "Global::Roundcr: Invalid decimal places."
    Roundcr = Number
End If

End Function

 

[DCrake]

If the source number is always to 3 decimal places then use

Round(10.325+.005,2)

 

[bulrush]

The problem is, in the debug window or my variable watch window, Access shows the number as 10.325 but it is actually storing 10.324999999. And the internal Round function does not actually round, it simply changes what is displayed, not the value that is stored.

Example:

dim amt as single, commrate as single
dim a as single, b as single
amt=295.0
commrate=.035
a=amt*commrate ' a=10.32499999 which does not match my calculator
b=round(a,2) ' This will show 10.32 but should be 10.33.

I'm doing calculations of dollars and cents in a report. And sometimes the results is a penny off because the Access internal Round does not change the value, it only changes the display, as does the text box Format and Decimals properties. Because the rounded amount is incorrect, further calculations based on this bad number are also incorrect.

 

[gemma-the-husky]

well then at the time you store the value you need to round it to suit.

the problem is partly caused by imprecision of the binary system - so you ought to store money values as CURRENCY data type - not single or double - and then you probably wont get this problem anyway.

but rounding in computers normally goes to the nearest EVEN number

so
5.325 rounds DOWN to 5.32
5.335 rounds UP to 5.34

to force different behavior you will need to write your own function.

 

[DCrake]

Ok

Expanded

?Round(Round(amt*commrate,3)+.005,2)

 

[vbaInet]

I think that will fail if you had 10.324 for example.

Perhaps something like:

round(10.325 + "0.00" & Abs(right(mid(10.325, instr(1, 10.325, "."), 4), 1) = 5), 2)

And a function:

Public Function SetDecimal(dblNumber As Double, intDecimal As Integer) As Double
    If intDecimal < 1 Or InStr(1, dblNumber, ".") = 0 Then
        SetDecimal = dblNumber
        Exit Function
    End If
    
    SetDecimal = Round(dblNumber + "0." & String(intDecimal, "0") & _
                       Abs(Right(Mid(dblNumber, InStr(1, dblNumber, "."), (intDecimal + 1)), 1) = 5), intDecimal)
    
End Function

 

[djnoser]

I use my own rounding function:

Function ROUNDED(Amt As Double, Places As Integer) As Double

ROUNDED = Int(Amt * 10 ^ Places + 0.5 + 0.1 ^ 10) / 10 ^ Places

End Function

Unlike the built in function, this also rounds to negative places [rounded(123456789,-2) = 123456800].