According to a posting I read in a newsgroup, MS Access 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.

(article continues after sponsor spot)

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. I will let you work on a solution.

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

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007 Richard W. Killey. All Rights Reserved.