Rounding up to 2 decimal places

[Craig Dovey]

I have a form where I have set the AfterUpdate property of two fields (QTY
(Double Number) and RATE (Currency)) to multiply the two numbers and round
to two decimal places and return the value to field AMOUNT

AMOUNT = Round ([QTY] * [RATE] , 2)

This works fine except for the majority of the time that the returned value
is exactly 0.005 or 0.015 etc. it will round the number down rather than up

For example when QTY is 1.19 and RATE is 2817.5 it will return 3352.82
rather than 3352.83

Any ideas?

 

[Brian]

I have a form where I have set the AfterUpdate property of two fields (QTY
(Double Number) and RATE (Currency)) to multiply the two numbers and round
to two decimal places and return the value to field AMOUNT

AMOUNT = Round ([QTY] * [RATE] , 2)

This works fine except for the majority of the time that the returned value
is exactly 0.005 or 0.015 etc. it will round the number down rather than up

For example when QTY is 1.19 and RATE is 2817.5 it will return 3352.82
rather than 3352.83

Any ideas?

That's 'cos it's using bankers' rounding. See:

http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

 

[Steve Schapel]

Craig,

I don't think your statement of "the majority of the time" is correct.
The Round() function rounds to the nearest even digit, e.g. 3352.825
rounds to 3352.82, while 3352.835 rounds to 3352.84, which means that
exact multiples of .005 are rounded up 50% of the time, and down 50% of
the time, which is how it should be.

If you want it to always round up, I think you can use this...
Int([QTY]*[RATE]*100+0.5)/100