Query calculation problem

[smallWorld]

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!

 

[pbaldy]

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.

 

[smallWorld]

Thanks for your reply!

 

[smallWorld]

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!

 

[gemma-the-husky]

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

 

[pbaldy]

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