Rounding off

[Alex Motilal]

Hi,
I have created a Raw Cotton Purchase database. Raw cotton is purchased directly from regulated markets in India from farmers @ rates per quintal. In the purchase statement the total quintals and rate is shown and the value is shown in Rounded off Rupees. The syntax used in the underlying query is =Round([quantity]*[rate],0).
If the result is 150,100.48, it should display as 150,100 and if the result is 150,100.50, it should display 150,001.
My problem is if the result is 150,100.51, it displays 150,101. However, if the result is 150,100.50 it is not rounding to the higher value and displays the result as 150,100 instead of 150,101. This error is not in Excel.
Can anyone can help me in identifying what mistake I am making.
Thanks in advance.

[NTC]

if the result is 150,100.50, it should display 150,001 ????

well that isn't math

"My problem is if the result is 150,100.51, it displays 150,101".....yes that is the correct rounding !!!

Access is going to give you a mathematically correct rounding....so it isn't Access' fault here.....you are going to need to implement a custom algorithm....it is not reasonable for you to expect any software to do anything other than correct math in its embedded functionality....

[Alex Motilal]

Thank you very much.
Access is correct in rounding off 150,000.51 to 150,001/-. However you have not explained why it is not rounding off 150,000.50 to 150,001/-. In Excel it is not so.
I'll be very happy if you could explain this difference of mathematical calculations in Access and Excel.
Thanks again for the response.

[RuralGuy]

Unlike Excel, the Access Round() function uses "Banker's rounding". I would recommend creating your own function as I do.

[NTC]

well - - I think your question is more about the field definition at the table level...if it is defined as an Integer - it will round. If defined as currency it will be 2 decimal points...you can define it in several ways so it will not round at all...setting the number of decimal places that you want. This is the same as excel - - so the reason I think you see a difference is because the field property is defined differently.

Also - in Access - one has the ability to define the property at the form level (unlike excel) so therefore if your table field property is correct - double check the form level property as this is display only and does not affect what goes into the table....

hope this helps...