Sort letters berfore number

[revilord]

I have a field (PartNumber) in the talbe Parts that looks like this

A1234
B2345
12345
234357

I want to sort the table by PartNumber but have the parts with Letters sorted first. Example would be to sort A-Z then 0-9.

 

[PHV]

ORDER BY Abs(IsNumeric(PartNumber)), PartNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[revilord]

I tried
ORDER BY Abs(IsNumeric(PartNumber)), PartNumber

but didn"t get the sort i was looking for.

if I have PartNumber =
A1234
12340
000123
B23

It should look like this when sorted
A1234
B23
000123
12340

 

[PHV]

And this ?
ORDER BY IIf(PartNumber Like '[A-Z]*',0,1), PartNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[revilord]

PHV,
Thanks your sort would have worked except I didn't give you all the data combinations. The sort worked for the first character but the subsequent characters sorted as usual (numbers then letters).
i.e. some part numbers are like this

AD1234
ADC34522

and so on. I'm sure with a few modifications I could have got it to work.

The orginal data file was sorted correctly but had 2 record types in it which forced me to import the records separately. I modified the records so I only needed to import them once so I no long need to sort. Thanks for the help.