Question Huge decimal places when subtracting two numbers

[sanal]

In my database table there are two fields both field size are double and decimal places auto. When I subtract 67 from 76.07 the result seen as 9.06999999999999 in another field which has the same field size, instead of showing the correct answer 9.07. Please inform me the reason for this. How can I correct it. I want to get the answer as 9.07 as we use a calculator. I request your valuable suggestions.

 

[theDBguy]

Hi. A double data type can contain a lot of decimal places. How many you see or display is just a matter of formatting. If you want to "always" only see two decimal places (and round the value up), try setting the Format property to Fixed with Decimal Places set to 2.

 

[June7]

See if this helps understanding https://stackoverflow.com/questions/588004/is-floating-point-math-broken

 

[arnelgp]

cast both numbers as Decimal. decimal datatype has more accuracy than double:

dim a as double
dim b as double
a=76.07
b=67
debug.print cdec(a) - cdec(b)

result: 9.07

 

[Mark_]

In addition to what the others have said, if your first value shows as 76.07 but was calculated as 76.06999999999999, then you are getting the right answer. This means we would need to know where both numbers are coming from to give a better answer.

76.07 could either be the actual value in your data OR it could be a displayed value that is rounded from your data based on the format of your control. If this is the former, Arnel has your answer. If it is the later, you'll need TheDBGuys' response.

 

[The_Doc_Man]

I took a quick look at June7's reference and it gives you a very accurate - but very technical - "why" of what is happening. It gets kind of wordy. If that was long enough to take you into the weeds and lose you, here is a shorter answer.

That long string of digits is caused by the fact that 1/10 = 1/2 * 1/5, 1/100 = 1/4 * 1/25, etc. Your number 9.07 contains 9 (an exact number) + 7/100 - and it is fraction that causes the problem.

The 1/2 and 1/4 are exact in binary, but because the 1/5 and 1/25 are NOT exact in binary, you run into the same problem as expressing 1/3 in decimal, = 0.3333...333_ out to as many places as you like. Well, 1/5 and its multiples lead to the same exact problem in binary because 1/5 is a fraction based on an odd number. I.e. representing an odd number in an even numeric base. That's where the long string of digits come from.

The fix is to recognize that you are confusing computation with display. Literally, you DO NOT CARE what the computation contains because in binary, that's all it will ever contain anyway. If you choose a format of 2 decimal places, the formatting code will round off the internal value to the closest number that can be displayed with that number of decimal places. A format such as, e.g. Format(number, "#####.##"), would fix that problem. The greater problem would be whether you wanted more or fewer digits for some other number. There are many places where you can adjust the displayed value including the formatting of a text box or query.

You MIGHT be tempted to use a function such as ROUND, but that actually doesn't help because if you store the ROUNDed number back into a SINGLE or DOUBLE, you have the same exact representation problem.

 

[Pat Hartman]

As long as I don't need more than four decimal places, I always use the Currency data type for values where I will do arithmetic. Don't confuse the Currency data type with the Currency format. Currency data type is a scaled integer so it doesn't have the problems like this one that occur with floating point and it also doesn't have the problems that the Decimal data type has although I think Decimal has been fixed in previous versions. The Currency format is just that, a format conventionally used to show money values.