Rounding Error

[GeoNelix]

When I use the Round Function in Access 2000 I come up with some discrepencies. If I try to round a number (data type is currency), say 24180.5, the result is 24180. Obviously the number should round UP to 24181. What is strange is that if I hard code in a number to read 24180.501 the resulting rounded figure is correct (24181). Also, if I hard code in a number to read 24181.5 the function works fine and returns 24812. Any help would be greatly appreciated!

 

[MikeAngelastro]

Geo,

I believe I know why, but it's no help. In the past, Access had no round function. So I wrote one. My round function gets the same results you are getting. To narrow down the reason, I tested the Int function, because to round a number correctly you must convert it to a long. This is where the error is. VB has trouble converting 24180.5 to an integer. Int(24180.5) gets 24180 and Int(24181.5) gets 24182. This looks like a bug in VBA. Incidentally the results are the same in VB6.

I believe that this is one for Microsoft.

Mike

 

[Jeff Bailey]

No, it's not a bug. The convention for rounding that MS uses is sometimes called 'rounding to the even number' and is a bit more sophisticated than just always rounding up.

7.5 gets rounded to 8 but 6.5 gets rounded to 6

This is an attempt to smooth out the error that creeps in when you round a set of numbers and add them (the theory is that the rounding up and down cancels out).

HTH

Jeff

 

[mdemarte]

Round to nearest penny

Here's an example using two fields [quantity] and [rate], where I needed to round to the nearest penny for a price:

Int(([quantity]*[rate]*100)+0.5)/100

HTH

 

[MikeAngelastro]

The problem with MS's approach is that it really should be consistent with what people do under manual situations because that is what people will expect when the computer does the rounding for them. People always round up when a number has a .5. This is what MS should do too.