Select APP_HULL, BOOKNUM, APP, BOOKREV 
FROM CABLES 
WHERE APP_HULL = '" & Me.cboHull & "' And BOOKNUM Is Not Null ORDER BY
APP_HULL, BOOKNUM, DWGSHT ASC

Above is my t-SQL statement used to instantiate a recordset using ADO in
Access 97(frontend) connecting to SQL 7.0. 
DWGSHT is a varchar datatype. I need the results to sort like this:
1
2
3
4
5
6A
6B
6C
7

and not like:
1
10
11
12
.
.
19
2
20
21

I tried CAST and CONVERT to convert DWGSHT to int. It failed because of
cases where DWGSHT = '6A' or '1093A'.


Your assistance is greatly appreciated!

Michael Sargent
Programmer Analyst

We found a solution using 'CASE' method......

SELECT APP_HULL, BOOKNUM, APP, DWGSHT 
CASE
WHEN DWGSHT LIKE '%[a-z]' THEN CONVERT(int,Left(DWGSHT,Len(DWGSHT)-1))
ELSE CONVERT(int,DWGSHT)
END
FROM CABLES
WHERE APP_HULL = Me.cboHull
ORDER BY APP_HULL,BOOKNUM,DWGSHT ASC
 
Thanks,

Michael Sargent
Programmer Analyst

"Pursue to do something so great for God that it's doomed to fail lest God
be in it!" - Horis Curry


-----Original Message-----
From: Sargent, Michael J [mailto:michael.sargent@b...]
Sent: Thursday, April 04, 2002 1:50 PM
To: ActiveX_Data_Objects
Subject: [activex_data_objects] How do we convert text to numeric in
T-SQL statement to sort nume ric ascending...........


Select APP_HULL, BOOKNUM, APP, BOOKREV 
FROM CABLES 
WHERE APP_HULL = '" & Me.cboHull & "' And BOOKNUM Is Not Null ORDER BY
APP_HULL, BOOKNUM, DWGSHT ASC

Above is my t-SQL statement used to instantiate a recordset using ADO in
Access 97(frontend) connecting to SQL 7.0. 
DWGSHT is a varchar datatype. I need the results to sort like this:
1
2
3
4
5
6A
6B
6C
7

and not like:
1
10
11
12
.
.
19
2
20
21

I tried CAST and CONVERT to convert DWGSHT to int. It failed because of
cases where DWGSHT = '6A' or '1093A'.


Your assistance is greatly appreciated!

Michael Sargent
Programmer Analyst