Rounding to 2 decimal place

[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?

 

[Allen Browne]

The Round() function in Access rounds to the nearest even number.
1.19 * 2817.5 is 3352.825 so it is rounded down toward the even digit (2).

Try:
? Round(0.025,2), Round(0.035,2)
The first is rounded down (towards the 2).
The second is rounded up (towards the 4).
The rounding is always towards the even digit.

The idea is to balance the rounding:
- 4 digits are always rounded down (1,2,3,4)
- 4 digits are always rounded up (6,7,8,9)
- the exact midway (5) gets rounded towards the even digit, so half the time
up and half the time down.

If you don't like the way it works, you can use a custom rounding function
like this one:
http://www.mvps.org/access/modules/mdl0054.htm