Corrective Numbering

[TheCandyman]

I have a list of values which i would like to sort better. They come out sorted this way using SQL on a MS Access DB when i sort by this field.

T1
T10
T11
T13
T2
T21
T22
T3
T4
T5

I would like them to be:

T1
T2
T3
T4
T5
T10
T11
T13
T21
T22

Is this even possible with classic ASP? Can I toss these into an array and get a better listing this way?

 

[tsuji]

[0] How about directly sort from the dbase? In the sql, you issue the order by (for ms access) on the substring of the field (x, say) of table (t, say) as integer (or numeric). This is the simplest case.
[tt]
select * from t order by cint(mid(x,2))
[/tt]
Only you've to assure x is not null and has at least one number after a single starting letter.

[1] Sorting resultset contains actually the same idea, but would be quite clumsy.

 

[TheCandyman]

I tried your code suggestion but end up with an error:

Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.

I believe this is because CInt() is not a SQL function -- it is an Access/VB/VBA function.

This might not be possible??

 

[tsuji]

>They come out sorted this way using SQL on a MS Access DB when i sort by this field.
I don't know... I see "MS Access" in the quote.

 

[guitarzan]

TheCandyman,

Did you verify that "x is not null and has at least one number after a single starting letter", as tsuji noted?

 

[TheCandyman]

Some have those codes and others don't. Maybe your right and a null value is creating the error.

 

[tsuji]

[2] If the resultset of the sql I posted figuratively is too large, this may be too restrictive or maybe just the right size, who know!
[tt]
select * from t where (not isnull(x) and isnumeric(mid(x,2))) order by cint(mid(x,2))
[/tt]
[0.1] Attention is also drawn to my precision
>[self][tt] Only you've to assure x is not null and has at least one number after a single starting letter.[/tt]
before complaining error...