DatabaseLessons.com 
'Serving the Microsoft® Access 


Rounding NumbersAccording 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.
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 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved.  Privacy Policy 