Rounding Up to next Dollar works for all numbers except one.

[JMR]

hi, Can anyone help with this issue? I have a very simple sample access 2010 db with 3 records that should be producing the same amount in a calculated field on a report, but are not. I cant figure out why my rounding formula for the CALCULATED TOTAL on the Purchase Form is rounding perfectly for ALL combinations of numbers EXCEPT the numbers in a certain record. The problem is totally reproduce-able. I have also created a report so you can easily see the issue. The three records have the same TOTAL PRICE, but one of them is rounding up to $8 when it should be staying at $7. Something in the data is not as it seems.



The file is 800K which is more than the 500K max (ridiculously small!) allowed so I cant attach it but will be happy to email the sample file to anyone that would like to see if they can figure out this very weird problem!

Thanks much!

Jmark@stayintouch.us

[CJ_London]

compact the db, then zip and upload

[JMR]

here is the zipped file. Duh! I should have thought of that myself! uugh!

[orange]

Try this in your calculated field

=Round([c_Item_Total_Price],2)

[JMR]

Try this in your calculated field

=Round([c_Item_Total_Price],2)

Im looking to round UP not off. the round function will round off. correct?

[CJ_London]

doubles do not always round quite as expected - it is a fact of life and not an access specific issue

see this link for the technical rationale.

https://randomascii.wordpress.com/20...-2012-edition/

in particular note this paragraph

Floating point math is not exact. Simple values like 0.1 cannot be precisely represented using binary floating point numbers, and the limited precision of floating point numbers means that slight changes in the order of operations or the precision of intermediates can change the result. That means that comparing two floats to see if they are equal is usually not what you want.

To solve the problem, because you have a multistep calculation and mixing double datatypes with currency datatypes, you need to control the calculation all the way through - in this case, round the c_net control, and whilst doing this, also protect against nulls

=round(nz([Item_Quantity],0).......

What level of precision you want to go to is up to you, would think 2 is ok for your purposes

[JMR]

Thank you for that clarification! It eases my mind that I am going crazy or found a bug in Microsoft Access. I will take your suggestions and try to carry the rounding all the way through instead of just at the ending. Thanks again! JM