Sorting Numbers in Access97

[JJM0926]

I need some help with sorting some part numbers that are
alphanumeric. I have a table with a part number field which are in
the following format--sw1000, sw1200, sw2000, sw2600, sw3000, sw21000,
sw30200 etc..
When I sort that field in the table I want them to be sorted in order
chronologically starting with the lowest value to the highest value.
When I use the sort feature in access it sorts them, but not from
least to greatest--it sorts all the "ones", then all the "two's", then
all the "threes's" etc., I want them sorted thousands, ten
thousands, twenty thousands, etc.
The first two characters of the part number are always going to be
alpha. Any insights would be greatly appreciated. Thanks.

[Gord]

On Feb 19, 2:24 pm, "JJM0926" <[e-mail]:

I need some help with sorting some part numbers that are
alphanumeric. I have a table with a part number field which are in
the following format--sw1000, sw1200, sw2000, sw2600, sw3000, sw21000,
sw30200 etc..
When I sort that field in the table I want them to be sorted in order
chronologically starting with the lowest value to the highest value.
When I use the sort feature in access it sorts them, but not from
least to greatest--it sorts all the "ones", then all the "two's", then
all the "threes's" etc., I want them sorted thousands, ten
thousands, twenty thousands, etc.
The first two characters of the part number are always going to be
alpha. Any insights would be greatly appreciated. Thanks.

Try something like this:

SELECT [Table1].[PartNo]
FROM Table1
ORDER BY CLng(Mid([PartNo],3));

[storrboy]

On Feb 19, 3:24 pm, "JJM0926" <[e-mail]:

I need some help with sorting some part numbers that are
alphanumeric. I have a table with a part number field which are in
the following format--sw1000, sw1200, sw2000, sw2600, sw3000, sw21000,
sw30200 etc..
When I sort that field in the table I want them to be sorted in order
chronologically starting with the lowest value to the highest value.
When I use the sort feature in access it sorts them, but not from
least to greatest--it sorts all the "ones", then all the "two's", then
all the "threes's" etc., I want them sorted thousands, ten
thousands, twenty thousands, etc.
The first two characters of the part number are always going to be
alpha. Any insights would be greatly appreciated. Thanks.

I've not had to do this before, but perhaps you could sort on a
calculated field.
Example...SELECT ....... ORDER BY CLng(Mid([PartNumber],3));

[Tim Marshall]

JJM0926 wrote:

The first two characters of the part number are always going to be
alpha. Any insights would be greatly appreciated. Thanks.

Sort on this calculated field:

val(mid(MyPartNumberField,3))

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[JJM0926]

On Feb 19, 1:33 pm, Tim Marshall
<[e-mail]:

JJM0926 wrote:

The first two characters of the part number are always going to be
alpha. Any insights would be greatly appreciated. Thanks.

Sort on this calculated field:

val(mid(MyPartNumberField,3))

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

What Gord had me tried work perfectly. I then changed that to a make-
table query and I got my new table sorted the way I wanted. Thanks
all.

[Larry Linson]

"JJM0926" <jj*****@gmail.comwrote

What Gord had me tried work perfectly. I then
changed that to a make-table query and I got my
new table sorted the way I wanted.

You may be disappointed to know that data in relational tables is, by
definition, unordered. Access has to display it in some order, but you can't
count on that order in every situation. If you want the data ordered in a
particular sequence, you need to retrieve it with a Query specifying the
Fields on which it is to be sorted (which will be represented in the
corresponding SQL statements as an ORDER BY clause).

However, to obtain the order that you wish, you must separate the "numeric"
part of the text Field, and convert it to a number, as you were instructed
and as you did.

Larry Linson
Microsoft Access MVP