DSum Error

[JoshB]

Hi All

I'm new here, but fairly well versed in Access. I have a quick question that is driving me nuts. Here's how you can reproduce the problem.

Create a table (Table1) with one field (num - Double)

Enter in the following data...

0.4
0.3
0.2
0.1

Now, go to the immediate window and enter ?DSum("num","Table1")

Answer is 1, all is well

Now, type in ?DSum("num","Table1") = 1

Answer is False.... wha????

?DSum("num","Table1") = "1" - True
?DSum("num","Table1") > 0.999999999999999 - True
?DSum("num","Table1") > 0.9999999999999999 - False

How is this possible? What am I missing?

 

[JoshB]

Playing around a bit further...

?DSum("num","Table1") > 0.99999999999999983353 = True
?DSum("num","Table1") > 0.99999999999999983354 = False
?DSum("num","Table1") > 0.9999999999999998335399999999999999999999 (as many 9's as you want) = True

 

[JoshB]

The plot thickens (sorry for continuous replies to myself).

If you initially enter the values into the table the other way...

0.1
0.2
0.3
0.4

?DSum("num","Table1") = 1 - True

aarrrggghhh!!! What???? :banghead: :banghead:

 

[spikepl]

1. Decimal numbers are not represented internally wih 100% precision
2. What you see on the screen, is not always what you have - the system formats decimal numbers for display with a given number of decimals, but what is displayed is not necessarily equal to the actual value
3. For decimal numbers the equality comparison "=" will therefore often fail: two numbers that "obviously are equal" for a human will not be equal for the stupid machine, that compares them bit by bit, so to speak
4. To compare two real numbers A and B and decide whether they are equal you need to do it like this

abs(A-B)< eps

where eps is some very small number such as 0.00001, determined by the precision you require (also reflected by you declaring the numbers as Single or Double). For type Currency, you get exact representation with up to 4 decimals.

 

[JoshB]

Thanks spikepl

That makes total sense, but doesn't hurt any less. The values are doubles representing percentages (0.4 = 40%). The code I am writing is trying to determine whether the total values = 100%, which they obviously do and don't at the same time.

Would I be better storing these values as longs and ensuring the total is 100? What is the common practice for storing percentages? I don't think the client requires fractions of a percent, but clients are clients, and apt to change their mind.

I understand your explanation that "Decimal numbers are not represented internally with 100% precision", but why is it that the order in which they are entered changes the answer? I can cope with the stupid machine being incorrect, but surely expecting consistency is not too much to ask?