Rounding Problem...

[avandorpe]

I've got a calculation that produces a 106.305 result that I am
rounding with 2 decimal places specified. The result is coming back
106.30...I'm no math genius but should it round to 106.31? Is this a
quirk/bug or am I missing something?

Any information would be appreciated...

 

[Jamie Collins]

Access uses what is known as "Banker's" rounding. Try googling this term

....and perhaps that will pick up the following from the archives:

Access uses "banker's rounding"

A (sweeping) misstatement.

The ROUND function indeed uses banker's rounding e.g.

SELECT ROUND(-12.345, 2)
returns -12.34 (rounded towards zero because the digit 4 is even).

as do some of the casting functions e.g.

SELECT CLNG(12.5), CLNG(13.5)
returns 12 and 14 respectively.

However, many other functions do not exhibit banker's rounding e.g.

SELECT FORMAT(-12.345, '00.00')
returns '12.35' (rounds away from zero)

SELECT FIX(-12.345)
truncates symmetrically

SELECT INT(-12.345)
truncates asymmetrically

CREATE TABLE Test (
cur_col CURRENCY NOT NULL,
dec_col DECIMAL(19,4) NOT NULL
)
;
INSERT INTO Test VALUES (0.55555, 0.55555)
;
SELECT cur_col, dec_col
FROM Test
;
returns 0.5556 (banker's rounding) and 0.5555 (symmetric truncation)
respectively.

[unquote]

Jamie.

--

 

[avandorpe]

Thanks guys for all the valuable info...I will let the powers that be
make their decision of how they want rounding handled by the app and
apply an appropriate solution.

 

[Jamie Collins]

I was responding to the OP's context, which clearly indicates Rounding in the
subject. While I can appreciate your enlightenment on this issue, the OP said
nothing about type conversion functions, INT, FORMAT, etc.

I agree the context on face value of both the OP and your reply is
'rounding' and I consider that a very general context, one that would
include type conversion (casting) functions, INT, FORMAT, implicit
rounding, etc. In other words, anything that involves values being
rounded. The following article considers, for example, casting
functions and the INT() scalar function in the context of 'rounding':

How To Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

If you mean for the context of your reply to be the ROUND() scalar
function then perhaps you could make that more explicit in future. Many
thanks for your consideration.

Jamie.

--