Odd results when searching for numeric range

[Darren Emery]

Not sure where to post this one - moderator please advise if there is a better location. 

 

Unfortunately the file I am working with was originally set up with a field that contains numbers as a text field.  I have a feeling that this is why I'm having trouble. (BTW - I did not create this problem, but inherited it!)

 

When searching for a number range that breaks across 1000, search returns no results.  A search x - 999, and 999 +, both return many results.  searching from 100...1500, nothing. 

 

Any ideas here?

 

 

[comment]

If the searched field is a Text field, then searching it will be in alphabetical order. Alphabetically, "999" is NOT between "100" and "1500".

 

It's difficult to say what's the best solution here, without knowing what do the values in the field represent.

[Darren Emery]

Thanks Comment - I thought it was something like this.  When you say alphabetically, as far as numerals, does that mean everything that starts wit a zero would be first, all leading ones next, etc? Therefore anything that starts with a 9 would be at the end of the order, regardless of place vale?

[comment]

does that mean everything that starts wit a zero would be first, all leading ones next, etc?

 

Yes. Alphabetically, "9" comes after "1500" - same as "b" comes after "azimuth".