Subtract bug (1 Viewer)

[Shinta]

Greetings:

I'm facing a weird bug where at a calculated field (which value is obtained by the subtraction of other previous fields at the same form, helped by VBA code), the result comes with a 1/10^15 (±0.000000000000001) difference.

So far, the calculations that have given me troubles (from about a year of production’s usage ), are:

(bugged) 8.2 - 7.3 = 0.899999999999999
(bugged) 8.2 - 7.4 = 0.799999999999999
(bugged) 8.2 - 7.5 = 0.699999999999999

(works) 8.3 - 7.3 = 1
(works) 8.3 - 7.4 = 0.9
(bugged) 8.3 - 7.5 = 0.800000000000001

I've uploaded an example DB for sake of asking for your kind help; thanks a lot in advanced.

Sincerely,

Javier

 

[Shinta]

Here it goes the file.

Thanks

 

[pbaldy]

I suspect the data type of your fields is either Single or Double, both of which are floating point numbers that will do that type of thing. I'd change the data type to Currency if you can, or force the issue in your calculation using CCur().

 

[Shinta]

Hello pbaldy, nice to meet you

My issue here would be that tbe net field (total value) is a weigth, so the "$" sign would bother in a way... any way to "trick" Access to use the currency criteria so the bug could be solved and, at the same time, the "$" sign is not shown?

Or, in any case.. any other suggestion?

By the way, the type of fields I'm using with all the values involved are "general number" (*not* float or double).

Thanks for the interest and quick responce; regards,


Javier

 

[pbaldy]

General Number is a format, not a data type. It's the data type/field size of the field in the table that I believe is the source of this problem. The $ is just a default format, you can override it. You can also try Decimal as the field size, or CDec() as a conversion function.

 

[spikepl]

This is not a "Bug". This is the way computers work with floating point numbers that are not represented exactly, because that would be impossible.

Whether you can use Currency depends on what is the maximum size of the numbers you exepct and their minimum numerical size. Currency is a fiddled integer number, i.e. represented exactly.

"general number" is not Type but Format -two entirely different things. To be rid of the currency sign in table fields with Type Currency just define their format as general number or standard number.

For calculated floating point numbers you can never ask whether a=b, because the precision enters into this. You can always ask wheter ABS(a-b) =< some very small number.

 

[Shinta]

Thanks Paul and spikepl for both answers:

I've finally understood the idea of the fields definition at the *table*. And yes, they were defined as Double with 2 decimals precition, and then the format at the form was General Number.

The combo that works is: field definition at Table = currency 2 decimals, and format at Form = General Number (so I get rid of the "$" sign).

What I could not use was the Decimal type, as it shows me only integer values (not fractions), even it says 18 decimal precition and 2 decimal shown numbers (???).

But well.. this info you gave me was not obvious for me. As I mentioned.. I had not faced this floating issue for more than a year of production calculations (!? ).

Thanks a lot!

Regards,

Javier

 

[pbaldy]

Happy to help!

 

[Pat Hartman]

There is an interesting article at www.fmsinc.com that explains the problem with floating point numbers (Single and Double). Look for "when Access math doesn't add up". As a matter of course, I define all non-integers as currency as long as 4 decimal digits are sufficient precision.