Rounding errors

[Bongard]

I am having some rounding issues with one of my queries. I have two
tables for which I am comparing interest rates. For now we'll just
call them table1.IntRate1 and Table2.IntRate2. The field types are
currency and limited to two decimals and I have checked the source
system to make sure they don't include any characters after the
hundreths digit and they are clean. I am trying to create a formula
field in my query that is "Diff:round([table1.IntRate1] -
[Table2.IntRate2]),0) However even when IntRate1=7 and IntRate2 = 6.5
the query is rounding this down to 0 instead of rounding .5 up to
1.... Does anyone have any ideas on why this might be and how I can
around this?


Thanks and much appreciated,
Brian

 

[Michel Walsh]

It uses the banking rounding:

? Round(0.5, 0), Round(1.5, 0), Round(2.5, 0), Round(3.5, 0)
0 2 2 4


but that happens only for exactly Int(x)+1/2. If it would always be rounding
to the next integer, at .5, eventually, someone would loose money (after
many transactions). For other decimal values, the rounding is as expected


? Round(0.6, 0)
1




Hoping it may help,
Vanderghast, Access MVP

 

[Bongard]

Oh ok, I have heard of this banking rounding before. Is there any way
to get around the banking rounding? Or just add an Iif statment saying
that iif(something) ends in .5 then to round up. I'd rather not have
to do that but it is an option.


Thanks,
Brian

 

[Bongard]

I figured out a solution to this bank rounding phenomenon. I used
val(format([MyField],0)) The format function will round in the correct
direction and then with val() I turn the field back into a number.

Hope this can help someone else!

 

[Jamie Collins]

I figured out a solution to this bank rounding phenomenon. I used
val(format([MyField],0)) The format function will round in the correct
direction and then with val() I turn the field back into a number.

Hope this can help someone else!

Well, Format() rounds 0.5 away from zero but VAL() coerces the result
to FLOAT (Double) e.g.

SELECT CCUR(-1234.5000) AS test_value,
VAL(FORMAT(test_value, '0')) AS result_value,
TYPENAME(result_value) AS result_type_name

returns 'Double'.

There are better (IMO) ways of achieving this without the casting/
coercing between types e.g.

SELECT CCUR(-1234.5000) AS test_value,
FIX(test_value) + SGN(test_value) AS result_value,
TYPENAME(result_value) AS result_type_name

Jamie.

--