Found a Bug or Error in Access 2002-2003 Round Function

[Dan K]

Okay this one's bizzarre...

I've been using the Round function for years and just realized that in the
(Access 2002-2003 format) when landing on a .5 the function is inconsistently
rounding up and down depending on the figure to the immediate left. The
function rounds up if the number to the left is even, and rounds down if the
number to the left is odd.

I've tested it on multiple databases and this happens regardless of location
of the decimal, the datatype of the column (integer or decimal), and whether
I use the actual round function or try to force a decimal into an integer
field/column.

Here are the results of the test I ran:

The query is a simple update of the left column using functions
Round([Decimal Number],0)

Decimal Number || Rounded Number (Dec Datatype) || Rounded Number (Int
Datatype)
0.5 0 0
1.5 2 2
2.5 2 2
3.5 4 4
4.5 4 4
5.5 6 6
6.5 6 6
7.5 8 8
8.5 8 8
9.5 10 10



And Function Round([Decimal Number],1):

Decimal Number || Rounded Number (Dec Datatype) || Rounded Number (Int
Datatype)
0.54 0.5 0
0.55 0.6 1
0.56 0.6 1
1.54 1.5 2
1.55 1.6 2
1.56 1.6 2
0.05 0 0
0.15 0.2 0
0.25 0.2 0
0.35 0.4 0
0.45 0.4 0
0.55 0.6 1
0.65 0.6 1
0.75 0.8 1
0.85 0.8 1
0.95 1 1

I tried compacting & repairing and also converted the Db to Access 2000 and
the same problem occured so I'm thinking it's got something to do with the
underlying code. Has anyone else noticed this error?

 

[Dan K]

So how come it's different in Excel? And how do you turn it off - or is there
another function that rounds consistently?

Thanks,

 

[Ken Sheridan]

For a summary of different rounding algorithms you might find the following
site of interest:


http://www.pldesignline.com/howto/showArticle.jhtml?articleID=175801189


Ken Sheridan
Stafford, England

 

[Ken Sheridan]

You could write your own. This should do it:

Public Function RoundHalfUp(dblNum As Double, intDecs As Integer) As Double

Dim strNum As String
Dim dblTemp As Double

dblTemp = dblNum + 0.5 * 10 ^ (intDecs * -1)

strNum = CStr(dblTemp)
On Error Resume Next
strNum = Left(strNum, InStr(strNum, ".") - 1) & _
Mid(strNum, InStr(strNum, "."), intDecs + 1)

RoundHalfUp = Val(strNum)

End Function

The second argument specifies the number of significant decimal places to
round to, so to round to an integer for example:

? RoundHalfUp(1.5,0)

which returns 2.

To round to one significant decimal place:

? RoundHalfUp(1.5,1)

which returns 1.5

but:

? RoundHalfUp(1.55,1)

returns 1.6

Ken Sheridan
Stafford, England