DMW logo
tel 01732 833085
tonbridge · kent · UK

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 2020-05-26 by David Wallis.


Applies To

This topic is relevant to Microsoft Access 97, 2000, 2002 (XP), 2003, 2007, 2010, 2013, 2016, 2019 and Microsoft 365.


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 in for use in Access:

Function dmwSymArithRound(_

ByVal Number As Variant, _

ByVal Places As Integer) _

As Double

Dim dblTemp As Double

dblTemp = CDec(Nz(Number))

dblTemp = CDec(dblTemp * 10 ^ Places)

dmwSymArithRound = _

Fix(dblTemp + 0.5 * Sgn(Number)) / 10 ^ Places

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 on 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) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification


Disclaimer

DMW Consultancy Ltd 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.

“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.