SQL SELECT to order Text in a numerical fashion

[Guest]

I need a SELECT statement that will order records in numerical order, but the
data type is Text (string).

For example, I have a table called "Block 600", which contains a column
called "TestPoint". Each cell in "TestPoint" is a number, like 1, 2, 3, 10,
11, 12, etc. But the Table schema defines "TestPoint" as Text datatype.
(Microsoft Access 2000).

When I issue the following SQL query:

"SELECT * FROM [Block 600] ORDER BY TestPoint"

I get the records sorted in alphabetical order: 1, 10, 11, 12, 2, 3
etc.
What I want is the records sorted in numerical order: 1, 2, 3, 10, 11, 12,
etc

Changing the database schema is not an option.

Is there a way to make the SQL query order the text in a numerical fashion ?

TIA

 

[Guest]

Hi.

In Access 2000, you can use a built-in VBA function in a SQL statement when
the original database design schema doesn't quite match your current needs.
Try:

SELECT *
FROM [Block 600]
ORDER BY CInt ([Block 600].[TestPoint]) ASC;

Your result will be 1, 2, 3, 10, 11, 12, ... in the TestPoint field.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[John Spencer (MVP)]

This is a good solution as long as there are no null values in the field.

IF you do have nulls in the field, then you can try forcing the conversion by
multiplying by 1

ORDER BY 1*[TestPoint]

or by testing for null and substituting a value

ORDER BY IIF([TestPoint] is null,9999999,CLng([TestPoint])

Hi.

In Access 2000, you can use a built-in VBA function in a SQL statement when
the original database design schema doesn't quite match your current needs.
Try:

SELECT *
FROM [Block 600]
ORDER BY CInt ([Block 600].[TestPoint]) ASC;

Your result will be 1, 2, 3, 10, 11, 12, ... in the TestPoint field.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

I need a SELECT statement that will order records in numerical order, but the
data type is Text (string).

For example, I have a table called "Block 600", which contains a column
called "TestPoint". Each cell in "TestPoint" is a number, like 1, 2, 3, 10,
11, 12, etc. But the Table schema defines "TestPoint" as Text datatype.
(Microsoft Access 2000).

When I issue the following SQL query:

"SELECT * FROM [Block 600] ORDER BY TestPoint"

I get the records sorted in alphabetical order: 1, 10, 11, 12, 2, 3
etc.
What I want is the records sorted in numerical order: 1, 2, 3, 10, 11, 12,
etc

Changing the database schema is not an option.

Is there a way to make the SQL query order the text in a numerical fashion ?

TIA

 

[Guest]

Thanks, John.

For ordinary data fields, your suggestion would be fine. But in this case,
the test point is a candidate key. It must always have a unique value and
can never be null, so I can't allow for nulls in the SQL. I'll keep your
suggestion in mind for other non-candidate key fields though.

Thanks again.

This is a good solution as long as there are no null values in the field.

IF you do have nulls in the field, then you can try forcing the conversion by
multiplying by 1

ORDER BY 1*[TestPoint]

or by testing for null and substituting a value

ORDER BY IIF([TestPoint] is null,9999999,CLng([TestPoint])

Hi.

In Access 2000, you can use a built-in VBA function in a SQL statement when
the original database design schema doesn't quite match your current needs.
Try:

SELECT *
FROM [Block 600]
ORDER BY CInt ([Block 600].[TestPoint]) ASC;

Your result will be 1, 2, 3, 10, 11, 12, ... in the TestPoint field.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

I need a SELECT statement that will order records in numerical order, but the
data type is Text (string).

For example, I have a table called "Block 600", which contains a column
called "TestPoint". Each cell in "TestPoint" is a number, like 1, 2, 3, 10,
11, 12, etc. But the Table schema defines "TestPoint" as Text datatype.
(Microsoft Access 2000).

When I issue the following SQL query:

"SELECT * FROM [Block 600] ORDER BY TestPoint"

I get the records sorted in alphabetical order: 1, 10, 11, 12, 2, 3
etc.
What I want is the records sorted in numerical order: 1, 2, 3, 10, 11, 12,
etc

Changing the database schema is not an option.

Is there a way to make the SQL query order the text in a numerical fashion ?

TIA