Sort Single/Double Digit Values

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a length table with a primary key id field and the length as shown
below in "DATA". When I execute my SQL below, the results can be seen in
"QUERY SORTING" below.

As you can see, the 8 foot length sorts to the bottom of the query because
the Access Data type is text type. Is there any way to run a SQL statement
that would sort the 8 foot value 1st and then display the other lengths in
correct order?

Any ideas?


' SQL Statement
--------------------------
SELECT lengthID, lengthName FROM tblLength ORDER BY lengthName


' QUERY SORTING Results

lengthID lengthName
--------------------------------
2 10'
3 12'
4 14'
5 16'
6 18'
7 20'
1 8'


' DATA - tblLength table data

lengthID lengthName
--------------------------------
1 8'
2 10'
3 12'
4 14'
5 16'
6 18'
7 20'
 
Scott said:
I have a length table with a primary key id field and the length as shown
below in "DATA". When I execute my SQL below, the results can be seen in
"QUERY SORTING" below.

As you can see, the 8 foot length sorts to the bottom of the query because
the Access Data type is text type. Is there any way to run a SQL statement
that would sort the 8 foot value 1st and then display the other lengths in
correct order?

Any ideas?


' SQL Statement
--------------------------
SELECT lengthID, lengthName FROM tblLength ORDER BY lengthName


' QUERY SORTING Results

lengthID lengthName
--------------------------------
2 10'
3 12'
4 14'
5 16'
6 18'
7 20'
1 8'


' DATA - tblLength table data

lengthID lengthName
--------------------------------
1 8'
2 10'
3 12'
4 14'
5 16'
6 18'
7 20'
Click to expand...


If all the [lengthName] values are specified in feet, and they always start
with the number as shown in your sample, then you SQL can be:

SELECT lengthID, lengthName FROM tblLength ORDER BY Val(lengthName)
 
that did the trick.

Dirk Goldgar said:
Scott said:
I have a length table with a primary key id field and the length as shown
below in "DATA". When I execute my SQL below, the results can be seen in
"QUERY SORTING" below.

As you can see, the 8 foot length sorts to the bottom of the query
because the Access Data type is text type. Is there any way to run a SQL
statement that would sort the 8 foot value 1st and then display the other
lengths in correct order?

Any ideas?


' SQL Statement
--------------------------
SELECT lengthID, lengthName FROM tblLength ORDER BY lengthName


' QUERY SORTING Results

lengthID lengthName
--------------------------------
2 10'
3 12'
4 14'
5 16'
6 18'
7 20'
1 8'


' DATA - tblLength table data

lengthID lengthName
--------------------------------
1 8'
2 10'
3 12'
4 14'
5 16'
6 18'
7 20'
Click to expand...


If all the [lengthName] values are specified in feet, and they always
start with the number as shown in your sample, then you SQL can be:

SELECT lengthID, lengthName FROM tblLength ORDER BY Val(lengthName)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top