Wrong result

[Alex Motilal]

In one of the queries the result shows as follows

1246.9 -1246.9 = -2.27373675443232E-13



Because of the above, in VBA if a fuction is based on the result which should show 0, it is giving wrong results. Certain results show correct. Is there any way to solve this?

Alex

[John_G]

It is because a computer cannot exactly represent (internally) 1246.9. Your comparizon is close to zero, but not quite - -2.27373675443232E-13 is a VERY small number, but still non-zero.
In our decimal based numbering system, .9 is exact, but a computer cannot represent .9 exactly - it can only be accurate to a certain number of decimal places. It's the same as in our decimal system; many (or even most) fractions cannot be represented exactly. For example, 1/3 is 0.33333... to infinity - it is still an approximation no matter how many 3's you use.

So, in your example above, while the expression looks like it should be 0, and mathematically it is 0, internally in the computer it isn't - there are leftover inaccuracies in the least-significant decimal places. However, when you test for 0, ALL decimal places must be 0's.

To get around this, instead of testing for 'zero' in non-integer numbers, test that the value you are checking is suitably close to zero for the purpose of your application. For example, you might decide that if two numbers A and B are equal to within 4 decimal places then the difference can be considered to be 0, you would test that the difference between them is less that that tolerance value:

If abs(A-B) < 0.0001 then they are equal.

To test if a variable is zero, you could put if abs(A - 0.0) < 0.0001 then...

I use ABS (absolute value) because you don't know (or care, really) if that small difference is positive or negative.

[CJ_London]

you can also use the int function or the round function

int(1246.9 -1246.9) will return 0
round(1246.9 -1246.9,1) will return 0

[John_G]

But -

int(1246.9 - 1246.0) will also (very misleadingly) return 0

With Round(), you would want to use a lot more decimal places than 1 - round(1246.91 -1246.92,1) will also (misleadingly) return 0.

[Micron]

I guess the data type is Double? Decimal might require you to set the Scale property to get expected results, but the common advice is to avoid decimal type anyway. Depending on the underlying data and desired level of precision, perhaps your query only needs a format for the desired number of places to the right of the decimal:
Result: Format([mynumber1]-[mynumber2],"0.00000")

[Alex Motilal]

Thanks John_G, your solution worked. Instead of 0 I used <0.0001 and it worked fine.