VBA Textbox comparisons providing illogical results

Question

Premise: Performing comparisons between two textboxes, one in a subform, one in a parent form, during the subform textbox's AfterUpdate event. When making this comparison, illogical results are returned.

Objective: Return legitimate comparisons between the values contained within these two controls.

Progress: The following code shows an example of code and apparent, illegitimate results outputted to the Immediate window.
Code:

Debug.Print IsNumeric(textBurnup) & IsNumeric(Parent!textBurnupLimit), _
            textBurnup & ">" & Parent!textBurnupLimit & " = " & _
             (textBurnup > Parent!textBurnupLimit)

textBurnup = Parent!textBurnupLimit - 100

Debug.Print IsNumeric(textBurnup) & IsNumeric(Parent!textBurnupLimit), _
            textBurnup & ">" & Parent!textBurnupLimit & " = " & _
           (textBurnup > Parent!textBurnupLimit)

Results:

TrueTrue      100>21500 = True
TrueTrue      21400>21500 = False`

Things I have tried:

Setting textbox format to 'general number'. Fixes problem but I dont want to have to do this.
Performing an arithmetic operation on either control. Fixes problem but I dont want to have to do this.
Using either controls' .Value property. Doesn't work.
Substituting a number in for the Parent!textbox. Works. Defeats the purpose of looking at the Parent's textbox.
A few other things I can't think of now because I am just too surprised that I managed to break logic.

The textboxes are both numbers and should be comparable. Why is this happening? Why does setting one of their formats to 'general number' fix it?

Related articles:
String Compare Logic (based on answers below)

Source Info:
Coding Horror (based on answers below)

In your example 100>21500 is True because it's a string ("ascii-betical") comparison, not a numeric one. `IsNumeric()` doesn't check to see if a variable value *is* a number, so `IsNumeric("100") = True` even though "100" is a string. If you want values pulled from to behave as numbers then the correct approach is 1)Use IsNumeric() to check they can safely be converted to a numeric type then 2) use `CDbl()`, `CLng()` etc to convert them as @simoco suggests. — Tim Williams, Apr 30 '14 at 20:54
Thanks @TimWilliams. Wouldn't 100 come before anything starting with a 2 ascii-betically making it 'less than'? Will setting controls as 'general number' make them be doubles/longs (seems easier than editting tons of code for ~20 forms)? Please supply an answer if you want so I can give the sweet karmas. Otherwise, baclkhawk is getting it :P — usncahill, May 01 '14 at 00:31
Answered my second question already. Setting all the textboxes to fixed or general number formatting solved it. — usncahill, May 01 '14 at 11:35

score 2 · Accepted Answer · answered May 01 '14 at 15:22

In your example 100 > 21500 is True because it's a string ("ascii-betical") comparison, not a numeric one.

One wrinkle in that though...

? "100" > "21500"  --> False!
? "100" >" 21500"  --> True!

I'm guessing your observed result was because you had a leading space on the second value: if that wasn't the case there's no way it would evaluate to True...

IsNumeric() doesn't check to see if a variable value is a number, just if it could safely be converted to one, so

IsNumeric("100") = True

even though "100" is a string.

If you want values pulled from to behave as numbers then the correct approach is

1) use IsNumeric() to check they can safely be converted to a numeric type then

2) use CDbl(), CLng() etc to convert them as @simoco suggests.

score 1 · Answer 2 · answered Apr 30 '14 at 21:21

"CDbl(textBurnup) > CDbl(Parent!textBurnupLimit)?" - simoco

"In your example 100>21500 is True because it's a string ("ascii-betical") comparison, not a numeric one. IsNumeric() doesn't check to see if a variable value is a number, so IsNumeric("100") = True even though "100" is a string. If you want values pulled from to behave as numbers then the correct approach is 1)Use IsNumeric() to check they can safely be converted to a numeric type then 2) use CDbl(), CLng() etc to convert them as @simoco suggests" - Tim Williams

VBA Textbox comparisons providing illogical results

2 Answers2