the Manx Man's MS Access Pages

Rounding Numbers (006)

According to a posting I read on a newsgroup, Access 97 uses "Banker's" rounding. I always called it Bill's rounding.

It rounds 5's to the nearest even number. examples:   (5.5 rounds to 6)   (6.5 rounds to 6)

If you want traditional 5's rounding up you can do this:

Xrounded = fix(X + 0.5)

This is fine if you want a whole number. What if you want something rounded to the closest 2 decimals?

Xrounded = fix(100 * X + 0.5) / 100

Better yet is a function that will allow rounding to a specified number of decimals.

Xrounded = fix(10^N * X + 0.5) / 10^N

Where N is an integer telling us how many decimal places we want.   Notice the additional functionality of this code.  If we pass it an    N = -1  it rounds to the closest 10 and if we pass it an   N = -2  it rounds to the closest 100.

One problem remains. This code does not handle negative numbers properly. It will say that -6.5 rounds to -6, whereas it should be -7.

Public Function mmRound(value As Double, decimals As Integer) As Double

     Dim D5 As Double

     If value < 0 Then
          D5 = -0.5
        Else
          D5 = 0.5
     End If

     mmRound = Fix(value * 10 ^ decimals + D5) / 10 ^ decimals

End Function