BUG: Inequality Comparison with Doubles in Conditional Statement

[eric_powell]

I REALLY, REALLY hope I'm not making a dumb mistake here. Basically, I'm doing a standard if-then statement, and the conditional expression is being falsely evaluated - or, at least, so it seems.

Here's the code:

If lhs > 0 Then
      If lhs <> rhs Then MsgBox "Could not reconcile the subtotal items for " & paidDate & "."
End If

That's not much go to on, and still left me feeling like I was crazy, but then I opened the immediate window and did this little exercise. It shows that although these variables (both doubles) seem to be equal, they don't evaluate as such. I'm wondering whether this isn't a decimal issue, but it would still be quite misleading as no decimals seem to be present.
Here's from the immediate window:

x = 2
?x
 2 
y = 2
?y
 2 
?x=y
True
?x>y
False
?y<x
False

?lhs
 129456 
?rhs
 129456 
?rhs=lhs
False
?lhs>rhs
True
?rhs>lhs
False

Oh, and btw, this same line of code has evaluated correctly dozens of times before. I have tried recompiling the module also.

 

[boblarson]

Given that a single and double are floating point numbers, it is probably decimal places that is causing you the grief. Are you actually using the decimal places somewhere else? Do you need them? If not change over to Long Integer.

 

[eric_powell]

Yes, I am using the decimals. I'm storing currency values, actually. It's just a coincidence that these are flat dollar amounts.

On that topic, though, if it were a decimal issue, why in the world wouldn't it be showing any decimals? Is that because the discrepancy lies in the nth decimal, and vba only PRINTS to something less than that?

Should I perhaps use the currency data type (I think there's one of those)?

Should I just round the numbers to the hundredth?

Any suggestions?

P.S. Thanks for the quick reply. It's much appreciated in these times of strife

 

[boblarson]

If you want to use currency, then you should use the CURRENCY datatype instead of double. Set the field datatype to Currency, or you can use the DECIMAL datatype and set the scale to 2.

As for the VBA, you haven't shown your Declarations so we can't be sure exactly what is happening.

 

[eric_powell]

OK, I've got it all squared away.

Here's some more from the immediate window. It appears that it was a decimal issue.

?round(lhs,2)=round(rhs,2) 
True
?lhs=rhs
False

I went ahead and changed the data types to Currency and that took care of it.

 

[gemma-the-husky]

the problem occurs because many real numbers cannot be completely represented in a
binary format. so testing for equivalence is likely to cause issues.