Getting wrong answer when comparing numbers

[cowboy]

I have a query that compares 2 numbers. The query works for almost all situations such as:


5.8'>5.7'
8'>7.5'
13.7'>11.9'

but it has some situations that when it evaluates the numbers it gets the following answers:
8.9'>10.8'
9.7'>10.6'
5.9'>18'

Why are these comparisons not working correctly?
What can I do to get the correct answers?

Thanks for help

[pbaldy]

Based on your examples, it appears the values are being treated as text. Check the data type of the field(s), or whether some function is being applied that causes the values to be seen as text (both the Nz and Format functions can do this for example).

[cowboy]

It is stored as a text, can you store a number and still have a foot sign? What does it compare when it considers it a text?

[pbaldy]

It does an alphabetic comparison, so given your example of

8.9'>10.8'

8 is greater than 1. Just as with an alphabetic sort, the comparison is character by character. I would store a number but add the foot sign for display to the user. That could be as simple as putting a label right next to the textbox displaying the number. If you want to store the values as text, you'll need to convert them to numeric values to do the comparison. Options include the various "C" functions (CDec, CDbl, CCur...) or a custom function.

[cowboy]

Any idea on how to convert 8.9' to a number in a query?

I just tried
LD: CDbl([Left Side DI Depth])
and it gave me #error, I am guessing it doesnt like the foot sign.

[cowboy]

I just tried
LD: Val([Left Side DI Depth])
and that seemed to give me back a number, let me test and see if this fixes everything and then I will post solved.

[cowboy]

Everything looks to be working well, thanks once again. Just for future info the Val() worked turning a text with foot sign into a number.

[pbaldy]

I forgot about Val(); glad you didn't!