treating Text like numbers

[damonc]

HI,
I have a field that contains mainly numbers, but the data type is set to Text because there are a few records where the numbers have a letter suffix.

Eg. Invoice numbers 100, 101, 102, 102A, 103 etc.

These suffixes are a result of poor data entry when the data was stored in Excel.

What I was to be able to do is sort this field and treat all entities as numbers. Currently when I sort it, it shows:

1
10
11
2
20
21
3
...etc

Is this possible, even though the data type is Text?

 

[EMP]

SELECT *
FROM [TableName]
ORDER BY Val([TextFieldName]);

^

 

[damonc]

Perfect! Thank you EMP