Fairly Easy: Text field comparisons

[JustSomeDude]

Hi again --

I'm not sure if this is possible, and was wondering if anyone might know...

I have a table with a field called "Control". This "Control" field is usually a number, but not always. Sometimes it is something like "1234-55" or "A1234". Therefore, it is set up as a Text field. I know I can do comparisons to display rows where the field is LIKE "123*" or "A1*"...but I am wondering if it is possible to do a comparison as if they were numbers.

For example, say I want to get all rows that are in the &quot;1000-2000&quot; range, but there are some with a value of &quot;120000&quot;. If I so a query that says LIKE &quot;1*&quot;, I'll be returned the 1000 range as well as the 120000 value. Is there any way I can do > or < type comparisons when the field is typed as TEXT? I've tried and it seems to sometimes almost work...but never consistantly.

I'm not sure about this one...

Any insight is very appreciated!

 

[psemianonymous]

To compare two numeric strings, use the Val() function (from the help file):

This example uses the Val function to return the numbers contained in a string.

Dim MyValue
MyValue = Val("2457")	' Returns 2457.
MyValue = Val(" 2 45 7")	' Returns 2457.
MyValue = Val("24 and 57")	' Returns 24.

So you could compare Val(str1) > Val(str2). Be careful to ensure both strings have numeric values using the isNumeric(str) function.

 

[JustSomeDude]

Perfect. Thanks...I knew in theory what I had to do, but not the syntax. This worked like a charm!