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?”
This topic is relevant to Microsoft Access 97, 2000, 2002 (XP), 2003, 2007, 2010, 2013 and 2016.
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 DMW's Excel-like rounding function in for use in Access:
ByVal Number As Variant, _
ByVal Places As Integer) _
Dim dblTemp As Double
dblTemp = CDec(Nz(Number))
dblTemp = CDec(dblTemp * 10 ^ Places)
dmwSymArithRound = _
Fix(dblTemp + 0.5 * Sgn(Number)) / 10 ^ Places
Please use the function on the understanding that you let us know of any circumstances under which it does not perform as intended.
Rounding Numbers in Microsoft Excel, Access and VBA is DMW’s piece on the whole subject of rounding.
Our description of the CDec() function appears on our page about floating-point numbers.
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.