contact@consultdmw.com

Excel-Like Rounding of Numbers in Microsoft® Access

“I am used to using the ROUND function in Excel to control the number of decimal places in the results of a formula, but I cannot find one in Access. Is there one?”

Last updated on 2024-04-26.

Response

There are a number of functions in Access that perform rounding, but none of these works in the same way as Excel's ROUND function.

For a full description of these functions, see Rounding Numbers in Microsoft Excel, Access and VBA.

Excel's ROUND function performs arithmetic rounding

Access's ROUND function performs bankers rounding.

This is an Excel-like rounding function for use in Microsoft Access:

Function fnDmwSymArithRound(Number&, DecPlaces%) As Double

Dim dbl#

dbl# = CDec(Nz(Number&))


dbl# = CDec(dbl# * 10 ^ DecPlaces%)

fnDmwSymArithRound = _

Fix(dbl# + 0.5 * Sgn(Number&)) / 10 ^ DecPlaces%


End Function

Please use the function on the understanding that you let us know of any circumstances under which it does not perform as intended.

Other References

Rounding Numbers in Microsoft Excel, Access and VBA is an article on the whole subject of rounding.

A description of the CDec() function appears in Floating Point Numbers and Scaling.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

Disclaimer

David Wallis does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.

AND/OR Queries Attaching labels Autonumber issues Compacting DB Converting MSA DB Convert 95 DB Convert 97 DB Convert 2003 DB Convert 2007 DB Create contacts DB Create dashboard Create search form Data modelling Dates and times
Date Picker Export to Excel Extract dates from strings Form design Form #Error Link FE to BE (1) Link FE to BE (2) List DB objects using query List DB forms List DB modules List DB queries List DB reports List DB tables
List field names Lost subforms Missing data Naming conventions Null values Party data model Party interrelationships Products data model Project management Rounding numbers VLookUp function 32 and 64 bit Access
BODMAS/BIDMAS Comparison operators Chart legend Conditional format COUNT, COUNTBLANK and COUNTA COUNTIF and COUNTIFS Create set of data Dropdown lists DSUM criteria Error 1004 Formula warnings Dates in Excel
Extract date from text Extract day from date Military date/time Month end New line in cell OFFSET Random colour fill Random number fill Random dates fill Range naming Rounding to 5 Separating names
SUMPRODUCT Summing time Tiling windows Time conversion Time intervals VBA introduction VBA function and sub procedures VBA error trapping VLOOKUP #DIV/0! error ∑ percentages
Templates Removing spaces Introduction to Styles Managing Styles Quick Parts & AutoText
Re-formatting documents Side-by-side paragraphs White space
MSA Export to Excel MSA Link Tables Excel Colour Fill Excel Programming
Excel Number Fill Excel Range Naming Excel Procedures Error Handling Select Case

“Your bread assumes the shape of the pan you use to bake your flour. Therefore stand still and know that you can't use a rounded pan and ever get squared bread. Change the pan and change the shape of the bread!”

Israelmore Ayivor, Shaping The Dream.

About DMW Mentoring
Freelance fees Fees calculator Data modelling Keyboard Shortcuts Managing email Real numbers Rounding numbers Spreadsheet design
Copyright © 2024 David M Wallis