Syntax: Round(expression[, numdecimalplaces])
The Access Round() function appears to be a handy way of rounding decimal values to a given number of decimal places. For example, Round(2.341, 2) returns 2.34.
However, it does not perform rounding the way you might expect. Take this example:
Round(2.385,2)
You would expect this to return 2.39, but if you open an immediate window and type ?Round(2.385,2) you will get the following output:
2.38 (not 2.39)
Even though the last digit was 5, it rounded DOWN.
If this is not the behaviour you expected, then here?s the explanation:
The Round() function does a round-to-even, not the more well-known round-to-larger. If the argument ends in 5, the number may be rounded up or down to achieve an even rightmost figure.
So in our example above:
2.385 could be rounded to either 2.38 or 2.39. The former has an even rightmost figure, so this is the result.
The reasoning behind this is to eliminate cumulative errors over a series of calculations. Put another way, 100 half pennies should round to be equal 50 cents, not 100 cents.
A RoundToLarger() function:
Public Function RoundToLarger(dblInput As Double, intDecimals As Integer) As Double
‘Implements a variant of the Round() function, that rounds-to-larger
‘rather than rounds-to-even:
Dim strFormatString As String ‘Format string
‘If input is zero, just return zero. Else format as appropriate:
If dblInput <> 0 Then
strFormatString = “#.” & String(intDecimals, “#”)
RoundToLarger = Format(dblInput, strFormatString)
Else
RoundToLarger = 0
End If
End Function
