Hi to all,

In my report, there is a calculated column called Cost that display as $13.13. The orginal value is 13.125. This column is used to calculate another field called leftover.

The expected result is $16.00 - $13.13 = $2.87
The actual result is $16.00 - $13.125 = $2.88. So there is a increase of $0.01.

The results in my report is run using a query. In the query, I set the format of the calculated field, Cost as $#,##0.00;($#,##0.00). How can I ensure that whatever it calculate is what's displayed? Meaning to say if it display 13.13, use it instead of using 13.125 to calculate the leftover value.

Thx!

Format is just that; a format. It doesn't affect the underlying value. Wrap your calculation in the Round function to round the value as desired.

Thanks for your reply!

According to http://www.techonthenet.com/access/functions/numeric/round.php

It says that the last digit after rounding is always an even number. How can I overcome it. Meaning to say if it's 13.75, it'll round to 13.8, if it's 13.85, it'll round to 13.9?


Thx!

the problem is probably storing an amount of 13.125 in the first place. why dont you round that off originally?

either int(amount+100)/100 will truncate

or clng(amount*100)/100 will round i think

Access uses what's been called "bankers" rounding, which is a bit quirky. Searching on that will probably turn up some discussions. I remember one here:

http://www.mdbmakers.com/forums/showthread.php?t=7139