treating Text like numbers

damonc

Registered User.
Local time
Today, 22:06
Joined
Oct 3, 2006
Messages
18
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?
 
SELECT *
FROM [TableName]
ORDER BY Val([TextFieldName]);

^
 
Perfect! Thank you EMP
 

Users who are viewing this thread

Back
Top Bottom