incorrect Rounding MsAccess 2003

[korsow]

In a query I use the following expr1 for calculating text fields
round(val(field1)*val(field2);3)
Issue is that in not all cases does it round correctly I have one calc where
the result without rounding = 5.7865 With rouding I would expect the result
to be 5.787 but in this case the resut is 5.786
Anyone any idea why this is?

 

[John W. Vinson]

In a query I use the following expr1 for calculating text fields
round(val(field1)*val(field2);3)
Issue is that in not all cases does it round correctly I have one calc where
the result without rounding = 5.7865 With rouding I would expect the result
to be 5.787 but in this case the resut is 5.786
Anyone any idea why this is?

This is what's called "Banker's Rounding". A number ending in 5 will round to
the nearest even digit in the next place - rather than always rounding up.
This ensures that the average of the rounded values is closer to the average
of the raw data, rather than being consistantly larger. It is not a bug and it
is not incorrect - it's just a different rounding convention than you're
assuming.

 

[Ken Sheridan]

As John has explained Access users 'banker's rounding' (rounding to even) by
default. You are expecting what's known as 'half up rounding'. You can
achieve this by adding 0.5 * 10^-n to the number to be rounded and then
truncating, where n is the number of significant decimal places to which you
are rounding. So when rounding to 3 decimal places you'd add 0.0005. So if
its 'half up rounding' you want, you could use a function such as:

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)
strNum = Left(strNum, InStr(strNum, ".") - 1) & _
Mid(strNum, InStr(strNum, "."), intDecs + 1)

RoundHalfUp = Val(strNum)

End Function

So:

RoundHalfUp(5.7865 ,3) returns 5.787

For a summary of the various rounding algorithms see:


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


Ken Sheridan
Stafford, England

 

[korsow]

John and Ken

Thanks for your input, very helpfull will apply your solutions

Ed