Sorting Question

[Tom]

Am running an Access 2K database in Access 2003. Have not converted it yet
and cannot for awhile until entire sales force has the new Laptops running
Access 2003.

My application deals with some 24,000+ part numbers that sometimes need to
be sorted in our part number sequence in Reports. Our part numbers involve
both numeric only number consisting of string lengths from 4 to 8 AND part
number that begin with Alpha characters (1 or 2 or 3 Alphas) then numeric
numbers with string lengths of 3 to 8. Some of these numbers have a hyphen
in them that skew the actual string length for sorting based on length. We
publish our price lists and put our products on the shelf based on the
length of the part number (up to the hyphen), then the sort.

I am looking for a way to sort our numeric numbers based on length and then
sort our Alpha/numeric parts up the hyphen in the part number when the
Report is printed.

If someone could point me in correct direction, it would be much
appreciated!

TIA!

 

[Jeff Boyce]

Tom

Without an example to help "see" what you're doing, this is only a guess...

It sounds like you want to sort your "part numbers" in alphabetic order. If
any of the part numbers start with numbers, those should sort before the
alpha-beginning numbers.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Tom]

Yes, we sort our numbers before our alpha numbers, but we do it based on
length within those groups, i.e.

1234
12345
123456

ABS12345
ABS123456
ATD123
ATD123C
ATD123M
ATD1234
ATD1234C
ATD1234M
BC1234
BC12345
BC123456
BH1234
BH12345
BH123456
CMA12345
CMA123456
H12
H12-2
H123
H123K
H123W
H123-2
H1234
H1234-2
H1234K
H1234W
H12345
H12345-2
H12345K
H12345W
MC1234
MC12345
MC123456
PGD123
PGD123C
PGD123M
PGD1234C
PGD1234M
SC12345
SC123456
SGD123
SGD123C
SGD123M
SGD1234
SGD1234C
SGD1234M
WC1234
WC12345
WC123456

There are more Alpha prefixes, but if I can sort this sample, the rest will
fall into place as well.

Thanks!

 

[tina]

I am looking for a way to sort our numeric numbers based on length and
then

sort our Alpha/numeric parts up the hyphen in the part number

i took the list of values you posted and dumped them into a single-field
table, then created a query on that table, as

SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1))
AS SortBy
FROM Table13;

the [x] field is the values, the SortBy field is a calculated field that
returns the [x] value when there's no hyphen, and the [x] value up to the
hyphen on all others. but what i'm seeing is that sorting by the SortBy
field returns the same order as sorting by the [x] field, so there's no need
to "get rid of" the hyphenated suffix before sorting.

can you post an example of how you *want* the previously posted list to be
sorted?

hth

 

[Tom]

Thanks for the reply.

The sort I want is numeric first based on the number of places, i.e. 2
digits in order, then 3 digits in order, then 4 digits in order, etc all
the way to 6 digits in order, each group in numberical order, not computer
order.

Then, the sort should deal with strings that begin with Alphas. Sort on
the Alpha letters first and then the numbers following the alpha, i.e.
Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6
digits. What I am after is very close to the example I offered although I
think I got some out of actual order. Some of the Alpha 5 digit have a
hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit.

Also, I was planning to put the sorting formula in a Report, probably in
the Sorting and Grouping area. BTW, the Field I want to sort is named
"Comp Part" if that helps!

Thanks

I am looking for a way to sort our numeric numbers based on length and then
sort our Alpha/numeric parts up the hyphen in the part number

i took the list of values you posted and dumped them into a single-field
table, then created a query on that table, as

SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1))
AS SortBy
FROM Table13;

the [x] field is the values, the SortBy field is a calculated field that
returns the [x] value when there's no hyphen, and the [x] value up to the
hyphen on all others. but what i'm seeing is that sorting by the SortBy
field returns the same order as sorting by the [x] field, so there's no need
to "get rid of" the hyphenated suffix before sorting.

can you post an example of how you *want* the previously posted list to be
sorted?

hth

 

[Tom]

To better answer your question, the sort should be able to handle the
following:

1
9
10
99
100
999
1000
9999
10000
99999
100000
999999

Alpha1
Alpha9
Alpha10
Alpha99
Alpha100
Alpha999
Alpha1000
Alpha9999
Alpha10000
Alpha99999
Alpha100000
Alpha999999

And some of the Alphas have hyphen number, i.e. H156666-2 or H4433-2.

Thanks

I am looking for a way to sort our numeric numbers based on length and then
sort our Alpha/numeric parts up the hyphen in the part number

i took the list of values you posted and dumped them into a single-field
table, then created a query on that table, as

SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1))
AS SortBy
FROM Table13;

the [x] field is the values, the SortBy field is a calculated field that
returns the [x] value when there's no hyphen, and the [x] value up to the
hyphen on all others. but what i'm seeing is that sorting by the SortBy
field returns the same order as sorting by the [x] field, so there's no need
to "get rid of" the hyphenated suffix before sorting.

can you post an example of how you *want* the previously posted list to be
sorted?

hth

 

[tina]

I am looking for a way to sort our numeric numbers based on length and
okay, hopefully i understood your goal better this time. paste the following
SQL statement into a new query's SQL pane, as

SELECT IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA,
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0) AS SortB, Table13.[Comp
Part]
FROM Table13
ORDER BY IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))),
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0), Table13.[Comp Part];

remember to replace Table13 with the correct table name, of course. if the
sort order fits your needs, then include the two calculated fields in the
"real" query that the report is based on. in the *report*, sort the data by
fields SortA, SortB, and [Comp Part], in that order. (note: don't bother
sorting the fields at the query level at all, it's the report level sort
that will "stick".)

hth

Thanks for the reply.

The sort I want is numeric first based on the number of places, i.e. 2
digits in order, then 3 digits in order, then 4 digits in order, etc all
the way to 6 digits in order, each group in numberical order, not computer
order.

Then, the sort should deal with strings that begin with Alphas. Sort on
the Alpha letters first and then the numbers following the alpha, i.e.
Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6
digits. What I am after is very close to the example I offered although I
think I got some out of actual order. Some of the Alpha 5 digit have a
hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit.

Also, I was planning to put the sorting formula in a Report, probably in
the Sorting and Grouping area. BTW, the Field I want to sort is named
"Comp Part" if that helps!

Thanks

I am looking for a way to sort our numeric numbers based on length and then
sort our Alpha/numeric parts up the hyphen in the part number

i took the list of values you posted and dumped them into a single-field
table, then created a query on that table, as

SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1))
AS SortBy
FROM Table13;

the [x] field is the values, the SortBy field is a calculated field that
returns the [x] value when there's no hyphen, and the [x] value up to the
hyphen on all others. but what i'm seeing is that sorting by the SortBy
field returns the same order as sorting by the [x] field, so there's no need
to "get rid of" the hyphenated suffix before sorting.

can you post an example of how you *want* the previously posted list to be
sorted?

hth

Yes, we sort our numbers before our alpha numbers, but we do it based on
length within those groups, i.e.

1234
12345
123456

ABS12345
ABS123456
ATD123
ATD123C
ATD123M
ATD1234
ATD1234C
ATD1234M
BC1234
BC12345
BC123456
BH1234
BH12345
BH123456
CMA12345
CMA123456
H12
H12-2
H123
H123K
H123W
H123-2
H1234
H1234-2
H1234K
H1234W
H12345
H12345-2
H12345K
H12345W
MC1234
MC12345
MC123456
PGD123
PGD123C
PGD123M
PGD1234C
PGD1234M
SC12345
SC123456
SGD123
SGD123C
SGD123M
SGD1234
SGD1234C
SGD1234M
WC1234
WC12345
WC123456

There are more Alpha prefixes, but if I can sort this sample, the rest will
fall into place as well.

Thanks!


On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce"

Tom

Without an example to help "see" what you're doing, this is only a guess...

It sounds like you want to sort your "part numbers" in alphabetic

order.
If

any of the part numbers start with numbers, those should sort before the
alpha-beginning numbers.

 

[Tom]

Tina,

When I trigger the Query, it's looking for a Parameter Value for x.

My Test Table is Called "Part" (Changed the Table Name in the SQL
Statement).

Field "Comp Part" is formatted as Text if it matters.

Thanks

okay, hopefully i understood your goal better this time. paste the following
SQL statement into a new query's SQL pane, as

SELECT IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA,
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0) AS SortB, Table13.[Comp
Part]
FROM Table13
ORDER BY IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))),
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0), Table13.[Comp Part];

remember to replace Table13 with the correct table name, of course. if the
sort order fits your needs, then include the two calculated fields in the
"real" query that the report is based on. in the *report*, sort the data by
fields SortA, SortB, and [Comp Part], in that order. (note: don't bother
sorting the fields at the query level at all, it's the report level sort
that will "stick".)

hth

Thanks for the reply.

The sort I want is numeric first based on the number of places, i.e. 2
digits in order, then 3 digits in order, then 4 digits in order, etc all
the way to 6 digits in order, each group in numberical order, not computer
order.

Then, the sort should deal with strings that begin with Alphas. Sort on
the Alpha letters first and then the numbers following the alpha, i.e.
Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6
digits. What I am after is very close to the example I offered although I
think I got some out of actual order. Some of the Alpha 5 digit have a
hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit.

Also, I was planning to put the sorting formula in a Report, probably in
the Sorting and Grouping area. BTW, the Field I want to sort is named
"Comp Part" if that helps!

Thanks

I am looking for a way to sort our numeric numbers based on length and
then
sort our Alpha/numeric parts up the hyphen in the part number

i took the list of values you posted and dumped them into a single-field
table, then created a query on that table, as

SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1))
AS SortBy
FROM Table13;

the [x] field is the values, the SortBy field is a calculated field that
returns the [x] value when there's no hyphen, and the [x] value up to the
hyphen on all others. but what i'm seeing is that sorting by the SortBy
field returns the same order as sorting by the [x] field, so there's no need
to "get rid of" the hyphenated suffix before sorting.

can you post an example of how you *want* the previously posted list to be
sorted?

hth


Yes, we sort our numbers before our alpha numbers, but we do it based on
length within those groups, i.e.

1234
12345
123456

ABS12345
ABS123456
ATD123
ATD123C
ATD123M
ATD1234
ATD1234C
ATD1234M
BC1234
BC12345
BC123456
BH1234
BH12345
BH123456
CMA12345
CMA123456
H12
H12-2
H123
H123K
H123W
H123-2
H1234
H1234-2
H1234K
H1234W
H12345
H12345-2
H12345K
H12345W
MC1234
MC12345
MC123456
PGD123
PGD123C
PGD123M
PGD1234C
PGD1234M
SC12345
SC123456
SGD123
SGD123C
SGD123M
SGD1234
SGD1234C
SGD1234M
WC1234
WC12345
WC123456

There are more Alpha prefixes, but if I can sort this sample, the rest
will
fall into place as well.

Thanks!


On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce"

Tom

Without an example to help "see" what you're doing, this is only a
guess...

It sounds like you want to sort your "part numbers" in alphabetic order.
If
any of the part numbers start with numbers, those should sort before the
alpha-beginning numbers.

 

[tina]

yes, looks like i missed replacing two of the [x] field names in the posted
SQL. just go through the SQL statement and replace those two x's with the
correct field name, hon.

hth

Tina,

When I trigger the Query, it's looking for a Parameter Value for x.

My Test Table is Called "Part" (Changed the Table Name in the SQL
Statement).

Field "Comp Part" is formatted as Text if it matters.

Thanks

I am looking for a way to sort our numeric numbers based on length and
then
sort our Alpha/numeric parts up the hyphen in the part number

okay, hopefully i understood your goal better this time. paste the following
SQL statement into a new query's SQL pane, as

SELECT IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA,
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0) AS SortB, Table13.[Comp
Part]
FROM Table13
ORDER BY IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))),
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0), Table13.[Comp Part];

remember to replace Table13 with the correct table name, of course. if the
sort order fits your needs, then include the two calculated fields in the
"real" query that the report is based on. in the *report*, sort the data by
fields SortA, SortB, and [Comp Part], in that order. (note: don't bother
sorting the fields at the query level at all, it's the report level sort
that will "stick".)

hth

Thanks for the reply.

The sort I want is numeric first based on the number of places, i.e. 2
digits in order, then 3 digits in order, then 4 digits in order, etc all
the way to 6 digits in order, each group in numberical order, not computer
order.

Then, the sort should deal with strings that begin with Alphas. Sort on
the Alpha letters first and then the numbers following the alpha, i.e.
Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6
digits. What I am after is very close to the example I offered although I
think I got some out of actual order. Some of the Alpha 5 digit have a
hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit.

Also, I was planning to put the sorting formula in a Report, probably in
the Sorting and Grouping area. BTW, the Field I want to sort is named
"Comp Part" if that helps!

Thanks



I am looking for a way to sort our numeric numbers based on length and
then
sort our Alpha/numeric parts up the hyphen in the part number

i took the list of values you posted and dumped them into a single-field
table, then created a query on that table, as

SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1))
AS SortBy
FROM Table13;

the [x] field is the values, the SortBy field is a calculated field that
returns the [x] value when there's no hyphen, and the [x] value up to the
hyphen on all others. but what i'm seeing is that sorting by the SortBy
field returns the same order as sorting by the [x] field, so there's

no
need

to "get rid of" the hyphenated suffix before sorting.

can you post an example of how you *want* the previously posted list

to
be

sorted?

hth


Yes, we sort our numbers before our alpha numbers, but we do it

based
on

length within those groups, i.e.

1234
12345
123456

ABS12345
ABS123456
ATD123
ATD123C
ATD123M
ATD1234
ATD1234C
ATD1234M
BC1234
BC12345
BC123456
BH1234
BH12345
BH123456
CMA12345
CMA123456
H12
H12-2
H123
H123K
H123W
H123-2
H1234
H1234-2
H1234K
H1234W
H12345
H12345-2
H12345K
H12345W
MC1234
MC12345
MC123456
PGD123
PGD123C
PGD123M
PGD1234C
PGD1234M
SC12345
SC123456
SGD123
SGD123C
SGD123M
SGD1234
SGD1234C
SGD1234M
WC1234
WC12345
WC123456

There are more Alpha prefixes, but if I can sort this sample, the rest
will
fall into place as well.

Thanks!


On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce"

Tom

Without an example to help "see" what you're doing, this is only a
guess...

It sounds like you want to sort your "part numbers" in alphabetic order.
If
any of the part numbers start with numbers, those should sort

before
the

alpha-beginning numbers.

 

[Tom]

Making progress. Now I get a "Data Mismatch in criterion expression" error
message.

SELECT IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp

Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA,
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0) AS SortB, Table13.[Comp
Part]
FROM Table13
ORDER BY IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))),
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0), Table13.[Comp

Part];

 

[Tom]

I got it to run by trimming down my test record size.

It seems to have hiccupped on the first two lines and later down the list.
The sort discount any -2 or Alpha suffixes as well.

Comp Part
H12
HS2
H102
H108
H111
H120
H122
H130
H131
H131-2
H132
H135-2
H136
H139
H140
H165
H186
H314
H321
H322
H323
H323-2
H324
H325
H325-2
H330
H333
H336
H337
H338
H341
H345
H345-2
H346
H347
H348
H350
H351
H352
H355
H356
H357
H358
H359
H360
H361
H403
H405
H407
H408
H411
H412
H413
H415
H423
H424
H425
H428
H429
H430
H433
H435
H438
H439
H440
H442
H446
H447
H448
H490
H495
H500
H509
H510
H521
H541
H546
H547
H551
H552
H557
H601
H602
H605
H608
H610
H611
H612
H615
H616
H617
H618
H703
H706
H707
H708
H1080
H1081
H1083
H1084
H1085
H1086
H1087
H1089
H1091
H1092
H1101
H1102
H1103
H1103-2
H1104
H1104-2
H1105
H1105-2
H1106
H1106-2
H1107
H1107-2
H1108
H1108-2
H1109
H1109-2
H1110
H1110-2
H1111
H1119
H1119-2
H1123
H1129
H1132
H1132-2
H1133
H1133-2
H1134
H1134-2
H1135
H1135-2
H1137-2
H1139
H1139-2
H1148
H1150
H1150-2
H1152
H1152-2
H1153
H1153-2
H1156
H1156-2
H1157
H1157-2
H1158
H1160
H1162
H1163
H1164
H1164-2
H1165-2
H1168
H1168-2
H1170
H1175
H1175-2
H1176
H1177
H1177-2
H1178
H1178-2
H1187-2
H1190-2
H1192
H1196
H1197
H1198
H1200
H1201
H1300-2
H1310-2
H1311-2
H1312-2
H1336-2
H1338-2
H1341-2
H1342-2
H1350-2
H1352-2
H1353-2
H1360
H1380-2
H1381-2
H1414
H1414-2
H1418
H1418-2
H1421
H1421-2
H1422
H1422-2
H1456
H1456-2
H1457
H1457-2
H1459
H1500-2
H1502-2
H1504-2
H1508
H1520
H1520-2
H1521
H1521-2
H1522-2
H1523-2
H1526-2
H1527-2
H1528-2
H1529-2
H1530-2
H1531-2
H1532-2
H1533-2
H1534
H1535
H1538-2
H1539-2
H1540
H1541
H1544-2
H1545-2
H1546-2
H1547-2
H1548
H1549
H1550
H1550-2
H1551
H1551-2
H1558
H1559
H1560-2
H1561-2
H1562-2
H1563-2
H1564
H1565
H1566
H1566-2
H1567
H1567-2
H1568
H1569
H1570
H1571
H1572
H1573
H1574
H1576
H1577
H1578
H1579
H1600
H1601
H1800
H1806
H1807
H1808
H1825
H1826
H1842
H1843
H1860
H1861
H1864
H1865
H1868
H1869
H1870
H1871
H1872
H1873
H1874
H1875
H1878
H1879
H1890
H1900
H1901
H1902
H1903
H1904
H1907
H1908
H1909
H1910
H1916
H1917
H2033
H2034
H2034-2
H2035
H2035-2
H2036
H2036-2
H2040
H2041
H2074
H2074-2
H2075
H2075-2
H2076
H2076-2
H2077
H2077-2
H2090
H2090-2
H2093
H2093-2
H2096
H2097
H2098
H2099
H2100
H2100-2
H2101
H2101-2
H2102
H2102-2
H2104
H2104-2
H2105-2
H2106
H2106-2
H2107
H2107-2
H2108
H2108-2
H2109
H2109-2
H2111
H2111-2
H2114
H2114-2
H2117
H2117-2
H2121-2
H2122-2
H2140
H2141
H2141-2
H2144
H2145
H2148
H2148-2
H2156-2
H2157
H2157-2
H2158
H2158-2
H2159-2
H2160-2
H2161
H2162
H2230
H2230-2
H2232
H2232-2
H2508
H2509
H2514
H2514-2
H2515
H2515-2
H2516
H2517
H2526
H2526-2
H2527
H2527-2
H2528
H2528-2
H2529
H2529-2
H2532
H2533
H2534
H2534-2
H2535
H2535-2
H2538
H2539
H2540
H2541
H2542
H2543
H2544
H2544-2
H2545
H2545-2
H2546
H2547
H2550
H2551
H2552
H2553
H2554
H2555
H2556
H2557
H2558
H2559
H2562
H2563
H2565
H2568
H2569
H2572
H2573
H2574
H2575
H2576
H2577
H2578
H2578-2
H2579
H2579-2
H2580
H2580-2
H2581
H2581-2
H2584
H2584-2
H2585
H2585-2
H2588
H2588-2
H2589
H2589-2
H2590
H2590-2
H2591
H2591-2
H2592
H2592-2
H2593
H2593-2
H2596
H2597
H2598
H2598-2
H2599
H2599-2
H2602
H2603
H2604
H2605
H2618
H2619
H2620
H2621
H2622
H2622-2
H2623
H2623-2
H2624
H2624-2
H2625
H2625-2
H2626
H2626-2
H2627
H2627-2
H2628
H2628-2
H2629
H2632
H2632-2
H2633
H2633-2
H2634
H2638
H2639
H2640
H2640-2
H2641
H2641-2
H2642
H2643
H2644
H2644-2
H2645
H2645-2
H2646
H2647
H2650
H2650-2
H2651
H2651-2
H2652
H2653
H2654
H2655
H2656
H2657
H2658
H2659
H2660
H2661
H2662
H2663
H2664
H2665
H2666
H2667
H2668
H2669
H2670
H2671
H2672
H2673
H2674
H2675
H2676
H2677
H2678
H2679
H2680
H2681
H2682
H2683
H2850-2
H2851-2
H2852-2
H2853-2
H2854-2
H2855-2
H2856-2
H2857-2
H2858-2
H2859-2
H2860-2
H2861-2
H2862-2
H2863-2
H2864-2
H2865-2
H2866-2
H2867-2
H2868-2
H2869-2
H2870-2
H2871-2
H2872-2
H3200-2
H3201-2
H3202
H3500
H3501
H3510
H3511
H3514
H3517
H3518
H3520
H3522
H3524
H3528
H3534
H3536
H3537
H3538
H3539
H3540
H3541
H3553
H3554
H3555
H3558
H3572
H3573
H3577
H3580
H3584
H3585
H3904-2
H4003-2
H4013-2
H4015
H4016-2
H4017-2
H4018-2
H4020-2
H4024-2
H4029-2
H4030-2
H4035-2
H4041-2
H4042-2
H4043-2
H4049-2
H4050-2
H4064-2
H4067-2
H4069-2
H4070-2
H4074
H4079-2
H4081
H4081-2
H4082
H4082-2
H4087
H4092
H4094-2
H4095-2
H4096-2
H4099
H4099-2
H4100
H4100-2
H4103
H4104
H4105
H4106
H4107
H4108
H5000-2
H5002
H5002-2
H5003-2
H5004
H5004-2
H5005-2
H5006
H5007
H5007-2
H5008
H5008-2
H5009
H5010
H5010-2
H5012-2
H5014-2
H5015
H5015-2
H5016
H5016-2
H5017-2
H5019
H5019-2
H5021
H5021-2
H5025
H5025-2
H5028
H5030
H5030-2
H5031
H5031-2
H5032
H5034
H5035
H5036
H5037
H5038
H5038-2
H5039
H5041
H5042
H5042-2
H5043
H5044
H5044-2
H5045
H5045-2
H5046
H5047
H5048
H5049
H5050
H5051
H5052
H5052-2
H5053
H5054
H5055
H5056
H5058
H5060
H5061
H5062
H5063
H5065
H5065-2
H5066
H5067
H5068
H5069
H5070
H5071
H5072
H5073
H5074
H5075
H5076
H5077
H5078
H5079
H5080
H5081
H5082
H5083
H5084
H5085
H5086
H5087
H5088
H5089
H5090
H5091
H5093
H5094
H5095
H5096
H5097
H5098
H5099
H5100
H5101
H5103-2
H5104
H5107
H5108
H5109
H5111
H5112
H5113
H5113-2
H5114
H5115
H5115-2
H5116
H5117
H5138
H5200-2
H5201-2
H5202-2
H5204-2
H5208
H5212-2
H5213
H5213-2
H5216
H5218
H5219-2
H5221-2
H5222-2
H5284
H5286
H5287
H5288
H5300-2
H5301
H5302
H5303
H5311
H5350
H5370
H5370-2
H5371
H5371-2
H5372
H5372-2
H5373
H5373-2
H5374
H5374-2
H5375
H5375-2
H5376
H5377
H5400
H5404
H5404-2
H5405-2
H5408
H5408-2
H5411
H5415
H5416
H5416-2
H5418
H5418-2
H5420
H5421
H5422
H5424
H5424-2
H5427
H5427-2
H5428
H5428-2
H5431
H5431-2
H5432
H5432-2
H5437-2
H5438-2
H5439
H5445
H5446
H5446-2
H5447
H5448
H5449
H5450
H5451
H5453
H5454
H5455
H5456
H5456-2
H5457
H5457-2
H5458
H5491
H5500
H5500-2
H5501
H5502
H5503
H5504
H5506
H5508
H5510
H5512
H5513
H5514
H5515
H5515-2
H5516
H5516-2
H5518
H5519
H5519-2
H5522
H5523
H5524
H5524-2
H5527
H5528
H5529
H5529-2
H5530
H5531
H5533
H5537
H5537-2
H5538
H5538-2
H5539
H5540
H5541
H5541-2
H5542
H5542-2
H5544
H5544-2
H5546
H5547
H5547-2
H5548
H5548-2
H5549
H5549-2
H5552
H5552-2
H5553
H5554
H5554-2
H5555
H5556
H5557
H5558
H5559
H5562
H5562-2
H5563
H5563-2
H5564
H5565
H5567
H5567-2
H5569
H5572
H5574
H5575
H5575-2
H5576
H5576-2
H5579
H5580
H5581
H5583
H5584
H5584-2
H5585
H5586
H5586-2
H5587
H5587-2
H5588
H5588-2
H5589
H5590
H5591
H5592
H5593
H5593-2
H5594
H5594-2
H5596
H5598
H5600
H5600-2
H5602
H5603
H5604
H5604-2
H5605
H5605-2
H5607
H5608
H5609
H5609-2
H5610
H5610-2
H5611
H5611-2
H5612
H5613
H5615
H5616
H5617
H5618
H5619
H5621
H5622
H5622-2
H5623
H5623-2
H5624
H5625
H5626
H5627
H5628
H5629
H5630
H5630-2
H5631
H5632
H5633
H5634
H5635
H5636
H5637
H5638
H5639
H5640
H5641
H5642
H5643
H5644
H5645
H5646
H5647
H5648
H5649
H5650
H5651
H5652
H5653
H5654
H5655
H5656
H5657
H5658
H5659
H5660
H5661
H5662
H5664
H5665
H5666
H5667
H5668
H5669
H5670
H5671
H5672
H5673
H5674
H5675
H5676
H5677
H5678
H5679
H5680
H5681
H5682
H5683
H5684
H5685
H5686
H5687
H5688
H5689
H5690
H5691
H5692
H5693
H5694
H5695
H5696
H5697
H5698
H5700
H5701
H5702
H5703
H5704
H5705
H5706
H5707
H5750
H5751
H5752
H5753
H5755
H5756
H5757
H5759
H5760
H5763
H7001
H7002
H7005
H7006
H7007
H7008
H7017
H7018
H7019
H7020
H7027
H7028
H7028-2
H7029
H7030
H7035
H7037
H7038
H7040
H7041
H7041-2
H7042
H7042-2
H7043
H7045
H7046
H7046-2
H7047
H7048
H7055
H7055-2
H7063
H7064
H7064-2
H7067
H7067-2
H7068
H7068-2
H7071
H7071-2
H7101
H7102
H7103
H7105
H7107
H7111
H7116
H7124
H7125
H7128
H7129
H7130
H7132
H7134
H7137
H7139
H7142
H7143
H7144
H7145
H7146
H7146-2
H7149
H7151
H7152
H7154
H7155
H7156
H7160
H7161
H7164
H7164-2
H7165
H7165-2
H7166
H7166-2
H7167
H7168
H7168-2
H7169
H7169-2
H7170
H7170-2
H7209
H7209-2
H7246
H7246-2
H7248
H7248-2
H7249
H7249-2
H7250
H7250-2
H7267
H7267-2
H7268
H7268-2
H7269
H7269-2
H7270
H7270-2
H7271
H7271-2
H7272
H7274
H7276
H7276-2
H7277
H7278
H7278-2
H7279
H7279-2
H7280
H7280-2
H7282
H7282-2
H7284
H7285
H7286
H7287
H7287-2
H7288
H7289
H7289-2
H7290
H7290-2
H7291
H7292
H7293
H7293-2
H7294
H7295
H7296
H7297
H7298
H7299
H7300
H7301
H7302
H7303
H7304
H7305
H7306
H7307
H7308
H7309
H7310
H7311
H7312
H7313
H7314
H7315
H7316
H7317
H7318
H7319
H7320
H7321
H7322
H7324
H7325
H7326
H7327
H7328
H7329
H7330
H8006-2
H8007-2
H8026
H8026-2
H8027-2
H8028
H8028-2
H8029
H8029-2
H8030-2
H8051-2
H8052-2
H8053-2
H8200
H8201
H8202
H8204
H8205
H8206
H8207
H8208
H8324
H8327
H8328
H8329
H9200
H9201
H9203
H9204
H9205
H9206
H9207
H9209
H9210
H9214
H9219
H9220
H9221
H9223
H9224
H9226
H9236
H9237
H9238
H9239
H9240
H9241
H9242
H9243
H9244
H9245
H9246
H9247
H9248
H9249
H9250
H9254
H9256
H9257
H9258
H9490-2
H9491-2
H10231-2
H10249-2
H10300-2
H10301-2
H10302-2
H10305-2
H10306-2
H10310-2
H10311-2
H10312-2
H10314-2
H10315-2
H10321-2
H10322-2
H10324-2
H10325-2
H10329-2
H10332-2
H10333-2
H10349-2
H10350-2
H10351-2
H10352-2
H10354-2
H10355-2
H10356-2
H10357-2
H10358-2
H10360-2
H10366-2
H10367-2
H10370-2
H10374-2
H10375
H10376
H10377
H10378
H10379
H10380
H10382
H10383
H10384
H10385
H10386
H10387
H10388
H10389
H10390
H10391
H10392
H10393
H10394
H10395
H10400-2
H10405-2
H10411-2
H10440-2
H11150
H11151
H11152
H11153
H11154
H11500
H11501
H11504
H11505
H11506
H11507
H11508
H11509
H11510
H11511
H11512
H11513
H11514
H11515
H11516
H11517
H12502
H12503
H12504
H12505
H12508
H12509
H12516
H12517
H12518
H12519
H12520
H12521
H12532
H12533
H12536
H12537
H12538
H12539
H12540
H12541
H12546
H12547
H13250-2
H1352A
H14000-2
H14001-2
H14002-2
H14003-2
H14007-2
H14008-2
H14011-2
H14012-2
H14015-2
H14016-2
H14018-2
H14020-2
H14021-2
H14022-2
H14024-2
H14025-2
H14026-2
H14028-2
H14030-2
H14032-2
H14033-2
H14049-2
H14050
H14055-2
H14057-2
H14059-2
H14065-2
H14066
H14068-2
H14069
H14070
H14071
H14072
H14073
H14074
H14075
H14076
H14077
H14078
H14079
H14080
H14081
H14082
H14083
H14084
H15001
H15001-2
H15002-2
H15003
H15004
H15005
H15005-2
H15006-2
H15008
H15009
H15009-2
H15013-2
H15014-2
H15020
H15020-2
H15022-2
H15031
H15031-2
H15032
H15034
H15035
H15037
H15038
H15039
H15039-2
H15040
H15043
H15044
H15044-2
H15045
H15049
H15051
H15052
H15054
H15054-2
H15055
H15056
H15057
H15058
H15059
H15060
H15060-2
H15061
H15062
H15063
H15065
H15066
H15067
H15068
H15069
H15071
H15072
H15073
H15074
H15075
H15076
H15077
H15078
H15079
H15080
H15081
H15083
H15084
H15084-2
H15085
H15087
H15088
H15090
H15091
H15095
H15095-2
H15096
H15097
H15098
H15099
H15100
H15101
H15102-2
H15103
H15104
H15105
H15107
H15108
H15110-2
H15119
H15119-2
H15120
H15120-2
H15121
H15121-2
H15122
H15127
H15127-2
H15128
H15130
H15131
H15132
H15133
H15134
H15135
H15136
H15137
H15138
H15139
H15140
H15142
H15143
H15144
H15148
H15149
H15151
H15152
H15153
H15154
H15155
H15156
H15157
H15158
H15159
H15160
H15161
H15163
H15164
H15165
H15166
H15167
H15168
H15169
H15170
H15171
H15172
H15173
H15174
H15175
H15176
H15177
H15178
H15179
H15180
H15181
H15182
H15183
H15184
H15185
H15186
H15187
H15188
H15189
H15190
H15191
H15192
H15193
H15194
H15195
H15196
H15197
H15198
H15199
H15200
H15201
H15351
H15351-2
H15352-2
H15405
H15405-2
H15409-2
H15410-2
H15411-2
H15416-2
H15417-2
H15418-2
H15420
H15420-2
H15422-2
H15423
H15423-2
H15436
H15436-2
H15461
H15463-2
H15464
H15465
H15466
H15468
H15469
H15470
H15471
H15500-2
H15501
H15501-2
H15503-2
H15504
H15504-2
H15506
H15506-2
H15507
H15507-2
H15510-2
H15511-2
H15513-2
H15514-2
H15516
H15517-2
H15518
H15519-2
H15522
H15522-2
H15524
H15524-2
H15527-2
H15530
H15530-2
H15532
H15532-2
H15534
H15534-2
H15535
H15535-2
H15536
H15536-2
H15538-2
H15539
H15539-2
H15540-2
H15541-2
H15542-2
H15543-2
H15544-2
H15546
H15550
H15550-2
H15553-2
H15601
H15601-2
H15603
H15604
H15606
H15607
H15608
H15609
H15611
H15613
H15614
H15615
H15617
H15619
H15619-2
H15620
H15620-2
H15622-2
H15624
H15626
H15630
H15631
H15633
H15634
H15634-2
H15635
H15635-2
H15636
H15636-2
H15637
H15638
H15638-2
H15640
H15640-2
H15642
H15643
H15643-2
H15644
H15644-2
H15646
H15648
H15650
H15651
H15652
H15654
H15655
H15655-2
H15656
H15657
H15658
H15659
H15660
H15661
H15661-2
H15663
H15663-2
H15666
H15667
H15670
H15671
H15671-2
H15672-2
H15673
H15675
H15676
H15677
H15678
H15679
H15680
H15681
H15682
H15683
H15684
H15685
H15687
H15688
H15689
H15690
H15691
H15692
H15693
H15695
H15696
H15697
H15698
H15699
H15699-2
H15700
H15700-2
H15701
H15703
H15703-2
H15704
H15704-2
H15705
H15706
H15706-2
H15708
H15709
H15710
H15711
H15712
H15714
H15715
H15716
H15716-2
H15717
H15718
H15719
H15719-2
H15720
H15721
H15722
H15723
H15724
H15725
H15726
H15727
H15728
H15729
H15729-2
H15730
H15730-2
H15731
H15732
H15733
H15736
H15737
H15740
H15741
H15742
H15743
H15743-2
H15744
H15745
H15745-2
H15746
H15747
H15747-2
H15748
H15748-2
H15750
H15751
H15752
H15753
H15753-2
H15754
H15755
H15756
H15758
H15759
H15760
H15761
H15762
H15763
H15765
H15766
H15769
H15770
H15771
H15772
H15773
H15774
H15775
H15776
H15777
H15778
H15779
H15780
H15781
H15783
H15784
H15785
H15786
H15787
H15788
H15789
H15791
H15792
H15793
H15794
H15795
H15796
H15797
H15798
H15799
H15800
H15801
H15802
H15803
H15804
H15805
H15806
H15807
H15808
H15809
H15810
H15811
H15812
H15813
H15814
H15815
H15816
H15817
H15820
H15821
H15822
H15823
H15824
H15825
H15826
H15827
H15828
H15829
H15830
H15831
H15832
H15833
H15834
H15835
H15836
H15838
H15839
H15840
H15841
H15842
H15843
H15844
H15845
H15846
H15847
H15848
H15849
H15850
H15851
H15852
H15853
H15854
H15855
H15856
H15857
H15858
H15859
H15860
H15861
H15862
H15863
H15864
H15865
H15866
H15867
H15868
H15869
H15870
H15871
H15873
H15874
H15875
H15876
H15877
H15878
H15879
H15880
H15881
H15885
H16000
H16003
H16004
H16005
H16006
H16007
H16008
H16009
H16010
H16011
H16013
H16014
H16015
H16016
H16017
H16019
H16020
H16021
H16022
H16023
H16024
H16025
H16026
H16027
H16028
H16029
H16031
H16033
H16034
H16035
H16036
H16037
H16038
H16039
H16040
H16042
H16043
H16044
H16046
H16048
H16049
H16050
H16051
H16052
H16053
H16054
H16056
H16057
H16059
H16060
H16061
H16062
H16064
H16065
H16066
H16067
H16068
H16069
H16070
H16072
H16073
H16074
H16076
H16077
H16078
H16079
H16080
H16081
H16083
H16085
H16087
H16088
H16089
H16090
H16091
H16092
H16093
H16094
H16095
H16096
H16097
H16098
H16099
H16100
H16101
H16102
H16103
H16104
H16105
H16106
H16107
H16108
H16109
H16111
H16112
H16113
H16125
H16131
H16132
H16133
H16134
H16135
H16136
H17101
H17102
H17103
H17104
H17105
H17106
H17107
H17109
H17110
H17111
H17112
H17113
H17114
H17115
H17116
H17117
H17118
H17119
H17120
H17120-2
H17122
H17122-2
H17124
H17125
H17131
H17133
H17133-2
H17137
H17137-2
H17140
H17140-2
H17141
H17142
H17142-2
H17145
H17145-2
H17146
H17147
H17147-2
H17148
H17148-2
H17157
H17158
H17159
H17160
H17162
H17163
H17163-2
H17184
H17186
H17187
H17187-2
H17196
H17197
H17198
H17199
H17211
H17214
H17233
H17236
H17240
H17242
H17253
H17254
H17258
H17260
H17262
H17265
H17267
H17269
H17270
H17272
H17272-2
H17274
H17274-2
H17275
H17276
H17276-2
H17277
H17278
H17279
H17279-2
H17280
H17280-2
H17281
H17283
H17285
H17285-2
H17287
H17287-2
H17289
H17290
H17290-2
H17291
H17291-2
H17292
H17293
H17294
H17295
H17296
H17297
H17298
H17299
H17300
H17301
H17302
H17302-2
H17303
H17304
H17305
H17306
H17306-2
H17307
H17308
H17309
H17310
H17312
H17314
H17316
H17317
H17319
H17320
H17321
H17322
H17323
H17324
H17325
H17326
H17326-2
H17327
H17328
H17329
H17329-2
H17330
H17331
H17332
H17333
H17333-2
H17334
H17335
H17336
H17337
H17340
H17341
H17342
H17343
H17344
H17345
H17346
H17347
H17348
H17350
H17351
H17352
H17353
H17354
H17355
H17356
H17357
H17358
H17359
H17360
H17361
H17362
H17363
H17364
H17365
H17366
H17367
H17368
H17369
H17370
H17371
H17372
H17373
H17374
H17375
H17376
H17377
H17378
H17379
H17380
H17381
H17382
H17383
H17384
H17385
H17386
H17387
H17999
H5311A
H5500A
H5501A
H5502A
H5503A
H5504A
H5506A
H5508A
H5510A
H5512A
H5514A
H5515A
H5516A
H5518A
H5519A
H5523A
H5524A
H5527A
H5528A
H5529A
H5530A
H5533A
H5537A
H5538A
H5540A
H5541A
H5542A
H5544A
H5546A
H5547A
H5548A
H5549A
H5552A
H5554A
H5556A
H5557A
H5562A
H5563A
H5564A
H5565A
H5567A
H5569A
H5572A
H5574A
H5575A
H5576A
H5579A
H5580A
H5581A
H5583A
H5584A
H5585A
H5586A
H5587A
H5588A
H5589A
H5590A
H5591A
H5592A
H5593A
H5594A
H5598A
H5600A
H5602A
H5603A
H5604A
H5605A
H5607A
H5608A
H5609A
H5610A
H5611A
H5612A
H5613A
H5615A
H5617A
H5619A
H5621A
H5622A
H5623A
H5624A
H5625A
H5626A
H5627A
H5628A
H5629A
H5630A
H5631A
H5632A
H5633A
H5634A
H5635A
H5636A
H5637A
H5638A
H5639A
H5641A
H5642A
H5643A
H5644A
H5645A
H5646A
H5647A
H5648A
H5649A
H5650A
H5651A
H5652A
H5653A
H5654A
H5655A
H5656A
H5657A
H5658A
H5659A
H5660A
H5661A
H5662A
H5664A
H5665A
H5666A
H5667A
H5668A
H5669A
H5670A
H5671A
H5672A
H5673A
H5674A
H5675A
H5676A
H5677A
H5678A
H5679A
H5680A
H5681A
H5682A
H5683A
H5684A
H5685A
H5686A
H5687A
H5688A
H5689A
H5690A
H5691A
H5692A
H5693A
H5694A
H5695A
H5696A
H5697A
H5698A
H5699A
H5704A
H5705A
H5706A
H5707A
H5750A
H5751A
H5752A
H5753A
H5754A
H5755A
H5756A
H5757A
H5758A
H5759A
H5760A
H5761A
H5762A
H5763A
H5764A
H5765A
H5766A
H5767A
H5769A
H5770A
H5773A
H15002F
H15042F
H15108A
H15122A
H15143A
H15422F
H15461A
H15501A
H15504A
H15506A
H15507A
H15510A
H15516A
H15519A
H15522A
H15524A
H15532A
H15534A
H15535A
H15536A
H15539A
H15542A
H15543A
H15546A
H15550A
H15553A
H15601A
H15602A
H15602F
H15603A
H15604A
H15606A
H15607A
H15608A
H15609A
H15611A
H15612A
H15612F
H15614A
H15615A
H15617A
H15619A
H15620A
H15622A
H15622F
H15626A
H15632F
H15633A
H15634A
H15635A
H15636A
H15637A
H15638A
H15640A
H15642A
H15643A
H15644A
H15646A
H15648A
H15650A
H15651A
H15652A
H15654A
H15655A
H15656A
H15658A
H15659A
H15660A
H15661A
H15663A
H15670A
H15671A
H15672A
H15672F
H15673A
H15675A
H15677A
H15678A
H15680A
H15681A
H15682A
H15683A
H15684A
H15687A
H15688A
H15690A
H15691A
H15692A
H15695A
H15696A
H15697A
H15698A
H15699A
H15700A
H15701A
H15703A
H15704A
H15705A
H15706A
H15708A
H15709A
H15710A
H15711A
H15712A
H15714A
H15715A
H15716A
H15717A
H15718A
H15719A
H15720A
H15721A
H15722A
H15723A
H15725A
H15726A
H15727A
H15728A
H15729A
H15730A
H15731A
H15732A
H15733A
H15736A
H15737A
H15742A
H15743A
H15744A
H15745A
H15746A
H15747A
H15748A
H15750A
H15751A
H15752A
H15753A
H15754A
H15755A
H15756A
H15758A
H15759A
H15760A
H15762A
H15763A
H15765A
H15766A
H15769A
H15770A
H15771A
H15772A
H15773A
H15774A
H15775A
H15776A
H15777A
H15778A
H15780A
H15781A
H15783A
H15784A
H15785A
H15786A
H15787A
H15789A
H15791A
H15792A
H15793A
H15794A
H15795A
H15796A
H15797A
H15798A
H15799A
H15800A
H15801A
H15802A
H15803A
H15804A
H15805A
H15806A
H15807A
H15808A
H15809A
H15810A
H15811A
H15812A
H15813A
H15814A
H15815A
H15816A
H15817A
H15820A
H15821A
H15823A
H15824A
H15825A
H15827A
H15828A
H15829A
H15830A
H15831A
H15832A
H15833A
H15834A
H15835A
H15836A
H15838A
H15839A
H15840A
H15841A
H15842A
H15843A
H15844A
H15845A
H15846A
H15847A
H15848A
H15849A
H15850A
H15851A
H15852A
H15853A
H15854A
H15855A
H15856A
H15857A
H15858A
H15859A
H15860A
H15861A
H15862A
H15863A
H15864A
H15865A
H15866A
H15867A
H15868A
H15869A
H15870A
H15871A
H15872A
H15873A
H15874A
H15875A
H15876A
H15877A
H15878A
H15879A
H15880A
H15882A
H15883A
H15885A
H15886A
H15888A
H15890A
H15891A
H15892A
H15893A
H15894A
H15895A
HB89655

okay, hopefully i understood your goal better this time. paste the following
SQL statement into a new query's SQL pane, as

SELECT IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA,
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0) AS SortB, Table13.[Comp
Part]
FROM Table13
ORDER BY IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))),
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0), Table13.[Comp Part];

remember to replace Table13 with the correct table name, of course. if the
sort order fits your needs, then include the two calculated fields in the
"real" query that the report is based on. in the *report*, sort the data by
fields SortA, SortB, and [Comp Part], in that order. (note: don't bother
sorting the fields at the query level at all, it's the report level sort
that will "stick".)

hth

Thanks for the reply.

The sort I want is numeric first based on the number of places, i.e. 2
digits in order, then 3 digits in order, then 4 digits in order, etc all
the way to 6 digits in order, each group in numberical order, not computer
order.

Then, the sort should deal with strings that begin with Alphas. Sort on
the Alpha letters first and then the numbers following the alpha, i.e.
Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6
digits. What I am after is very close to the example I offered although I
think I got some out of actual order. Some of the Alpha 5 digit have a
hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit.

Also, I was planning to put the sorting formula in a Report, probably in
the Sorting and Grouping area. BTW, the Field I want to sort is named
"Comp Part" if that helps!

Thanks

I am looking for a way to sort our numeric numbers based on length and
then
sort our Alpha/numeric parts up the hyphen in the part number

i took the list of values you posted and dumped them into a single-field
table, then created a query on that table, as

SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1))
AS SortBy
FROM Table13;

the [x] field is the values, the SortBy field is a calculated field that
returns the [x] value when there's no hyphen, and the [x] value up to the
hyphen on all others. but what i'm seeing is that sorting by the SortBy
field returns the same order as sorting by the [x] field, so there's no need
to "get rid of" the hyphenated suffix before sorting.

can you post an example of how you *want* the previously posted list to be
sorted?

hth


Yes, we sort our numbers before our alpha numbers, but we do it based on
length within those groups, i.e.

1234
12345
123456

ABS12345
ABS123456
ATD123
ATD123C
ATD123M
ATD1234
ATD1234C
ATD1234M
BC1234
BC12345
BC123456
BH1234
BH12345
BH123456
CMA12345
CMA123456
H12
H12-2
H123
H123K
H123W
H123-2
H1234
H1234-2
H1234K
H1234W
H12345
H12345-2
H12345K
H12345W
MC1234
MC12345
MC123456
PGD123
PGD123C
PGD123M
PGD1234C
PGD1234M
SC12345
SC123456
SGD123
SGD123C
SGD123M
SGD1234
SGD1234C
SGD1234M
WC1234
WC12345
WC123456

There are more Alpha prefixes, but if I can sort this sample, the rest
will
fall into place as well.

Thanks!


On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce"

Tom

Without an example to help "see" what you're doing, this is only a
guess...

It sounds like you want to sort your "part numbers" in alphabetic order.
If
any of the part numbers start with numbers, those should sort before the
alpha-beginning numbers.

 

[Tom]

Tina,

Here is another example of the sort not being what I need.

The sort should begin with the ATD2 through ATD1000+, then go to the PGD
numbers and finally the SGD numbers, all in chronological order.

Thanks

Comp Part
PGD2
PGD4
PGD8
PGD9
SGD8
PGD11
PGD1R
PGD21
PGD27
PGD2M
PGD32
PGD37
PGD3R
PGD42
PGD44
PGD45
PGD48
PGD51
PGD54
PGD56
PGD59
PGD61
PGD71
PGD76
PGD77
PGD82
PGD8R
PGD96
PGD97
PGD99
PGD9M
SGD11
SGD14
SGD21
SGD27
SGD33
SGD36
SGD37
SGD42
SGD44
SGD45
SGD48
SGD56
SGD76
SGD9M
ATD50M
ATD52C
ATD52M
PGD100
PGD101
PGD104
PGD110
PGD112
PGD114
PGD117
PGD118
PGD130
PGD131
PGD132
PGD134
PGD135
PGD136
PGD137
PGD139
PGD141
PGD144
PGD145
PGD146
PGD147
PGD148
PGD14R
PGD156
PGD157
PGD158
PGD163
PGD164
PGD166
PGD169
PGD174
PGD175
PGD176
PGD177
PGD178
PGD180
PGD185
PGD186
PGD187
PGD188
PGD189
PGD193
PGD194
PGD195
PGD196
PGD197
PGD198
PGD200
PGD201
PGD204
PGD207
PGD20R
PGD212
PGD213
PGD223
PGD227
PGD228
PGD229
PGD230
PGD231
PGD233
PGD234
PGD235
PGD239
PGD242
PGD243
PGD244
PGD246
PGD247
PGD250
PGD251
PGD254
PGD255
PGD258
PGD260
PGD263
PGD265
PGD266
PGD272
PGD273
PGD274
PGD275
PGD277
PGD281
PGD285
PGD288
PGD290
PGD292
PGD293
PGD297
PGD299
PGD300
PGD301
PGD303
PGD305
PGD306
PGD309
PGD30A
PGD311
PGD314
PGD323
PGD325
PGD328
PGD329
PGD330
PGD331
PGD332
PGD336
PGD337
PGD339
PGD33R
PGD340
PGD342
PGD345
PGD349
PGD350
PGD351
PGD352
PGD355
PGD356
PGD361
PGD363
PGD364
PGD365
PGD366
PGD36R
PGD374
PGD383
PGD384
PGD389
PGD38M
PGD38R
PGD390
PGD398
PGD399
PGD39M
PGD39R
PGD400
PGD409
PGD410
PGD415
PGD418
PGD422
PGD434
PGD436
PGD437
PGD43A
PGD440
PGD443
PGD445
PGD448
PGD451
PGD458
PGD45A
PGD462
PGD463
PGD465
PGD468
PGD470
PGD471
PGD476
PGD477
PGD481
PGD482
PGD484
PGD488
PGD490
PGD500
PGD501
PGD502
PGD503
PGD504
PGD50M
PGD50R
PGD510
PGD511
PGD515
PGD527
PGD528
PGD52C
PGD52M
PGD52R
PGD530
PGD531
PGD532
PGD534
PGD536
PGD537
PGD540
PGD549
PGD54A
PGD54B
PGD551
PGD552
PGD553
PGD556
PGD562
PGD563
PGD567
PGD568
PGD571
PGD572
PGD573
PGD576
PGD579
PGD57A
PGD580
PGD583
PGD584
PGD587
PGD588
PGD595
PGD596
PGD598
PGD600
PGD601
PGD602
PGD604
PGD611
PGD612
PGD613
PGD617
PGD619
PGD621
PGD622
PGD627
PGD631
PGD635
PGD636
PGD637
PGD642
PGD643
PGD653
PGD657
PGD661
PGD665
PGD666
PGD668
PGD669
PGD672
PGD677
PGD680
PGD685
PGD691
PGD697
PGD698
PGD699
PGD700
PGD707
PGD714
PGD716
PGD721
PGD722
PGD723
PGD724
PGD726
PGD727
PGD729
PGD730
PGD741
PGD750
PGD754
PGD755
PGD771
PGD772
PGD773
PGD775
PGD787
PGD793
PGD798
PGD811
PGD813
PGD815
PGD817
PGD822
PGD823
PGD828
PGD829
PGD830
PGD831
PGD832
PGD835
PGD84M
PGD84R
PGD855
PGD862
PGD864
PGD865
PGD867
PGD868
PGD871
PGD87M
PGD87R
PGD885
PGD887
PGD892
PGD90M
PGD90R
PGD913
PGD91M
PGD91R
PGD925
PGD92M
PGD92R
PGD96A
PGD96M
SGD102
SGD112
SGD114
SGD117
SGD122
SGD132
SGD134
SGD139
SGD141
SGD144
SGD146
SGD156
SGD157
SGD163
SGD164
SGD166
SGD169
SGD174
SGD175
SGD176
SGD177
SGD178
SGD180
SGD187
SGD194
SGD196
SGD197
SGD204
SGD20M
SGD213
SGD223
SGD229
SGD230
SGD231
SGD233
SGD235
SGD239
SGD243
SGD246
SGD247
SGD265
SGD277
SGD285
SGD293
SGD300
SGD301
SGD305
SGD309
SGD30A
SGD314
SGD339
SGD342
SGD347
SGD350
SGD351
SGD352
SGD361
SGD364
SGD365
SGD366
SGD38M
SGD398
SGD39M
SGD400
SGD445
SGD45A
SGD463
SGD468
SGD488
SGD490
SGD50M
SGD515
SGD52M
SGD54A
SGD57A
SGD595
SGD622
SGD84M
SGD87M
SGD90M
SGD91M
SGD92M
SGD96M
ATD120M
ATD149M
ATD154C
ATD154M
ATD184M
ATD215M
ATD224M
ATD225M
ATD236M
ATD242C
ATD249C
ATD249M
ATD269C
ATD269M
ATD273C
ATD325C
ATD333C
ATD334C
ATD368M
ATD369C
ATD369M
ATD370C
ATD370M
ATD374C
ATD375C
ATD375M
ATD376C
ATD383C
ATD396C
ATD409C
ATD411M
ATD450M
ATD459C
ATD459M
ATD465C
ATD470C
ATD471C
ATD473C
ATD476C
ATD477C
ATD477M
ATD484C
ATD503C
ATD505C
ATD506C
ATD507C
ATD508C
ATD522C
ATD524C
ATD530C
ATD536C
ATD537C
ATD540C
ATD551C
ATD557M
ATD558C
ATD562C
ATD576C
ATD579C
ATD580C
ATD588C
ATD598C
ATD600C
ATD601C
ATD610C
ATD617C
ATD618C
ATD623C
ATD627C
ATD632C
ATD632M
ATD637C
ATD648C
ATD650C
ATD652C
ATD652M
ATD653C
ATD655M
ATD659C
ATD659P
ATD666C
ATD667C
ATD668C
ATD674C
ATD674M
ATD674P
ATD679C
ATD679M
ATD691C
ATD697C
ATD698C
ATD698P
ATD699C
ATD699P
ATD701M
ATD702C
ATD702M
ATD707C
ATD711C
ATD711M
ATD714C
ATD721C
ATD723C
ATD726C
ATD727C
ATD729C
ATD730C
ATD730M
ATD731C
ATD732C
ATD734M
ATD735M
ATD741C
ATD746C
ATD748C
ATD748M
ATD748P
ATD749C
ATD754C
ATD757C
ATD757M
ATD758C
ATD758M
ATD760C
ATD769M
ATD771C
ATD774C
ATD777M
ATD784C
ATD785C
ATD785P
ATD786M
ATD788M
ATD790C
ATD791C
ATD792C
ATD793C
ATD799C
ATD802M
ATD803C
ATD804C
ATD805C
ATD806C
ATD812C
ATD816C
ATD820C
ATD820M
ATD821M
ATD824C
ATD824M
ATD828C
ATD830C
ATD833C
ATD834C
ATD841C
ATD842M
ATD843C
ATD844C
ATD855C
ATD856C
ATD865C
ATD866C
ATD881C
ATD882C
ATD883C
ATD898M
ATD905C
ATD908C
ATD909C
ATD913C
ATD914C
ATD921C
ATD922C
ATD923C
ATD928M
ATD931C
ATD931M
ATD931P
ATD932C
ATD932P
ATD934C
ATD935C
ATD949M
ATD950M
ATD959C
ATD966M
ATD969C
ATD974C
ATD976C
ATD999C
PGD1004
PGD101M
PGD102R
PGD110M
PGD119M
PGD120M
PGD120R
PGD122R
PGD123M
PGD123R
PGD130M
PGD131M
PGD133M
PGD135M
PGD136A
PGD136M
PGD137M
PGD139H
PGD142M
PGD145A
PGD145M
PGD147A
PGD147M
PGD149M
PGD149R
PGD150M
PGD150R
PGD151M
PGD151R
PGD152M
PGD152R
PGD153M
PGD153R
PGD154C
PGD154M
PGD154R
PGD158M
PGD170M
PGD179R
PGD183H
PGD183M
PGD184M
PGD188M
PGD189M
PGD190M
PGD191M
PGD195M
PGD199M
PGD199R
PGD200M
PGD202M
PGD202R
PGD203H
PGD203M
PGD207M
PGD214M
PGD215M
PGD218M
PGD219M
PGD220M
PGD221M
PGD224M
PGD225M
PGD227A
PGD227M
PGD228M
PGD236M
PGD240M
PGD242C
PGD242M
PGD245M
PGD249C
PGD249M
PGD255M
PGD256C
PGD256M
PGD257M
PGD257R
PGD258M
PGD259M
PGD261M
PGD262M
PGD263M
PGD264M
PGD266M
PGD269M
PGD270M
PGD272M
PGD273C
PGD273M
PGD274M
PGD275M
PGD276M
PGD278M
PGD279M
PGD280A
PGD280B
PGD280C
PGD280M
PGD281M
PGD282M
PGD284M
PGD289M
PGD290M
PGD291M
PGD294M
PGD295M
PGD296M
PGD297M
PGD298M
PGD299M
PGD303M
PGD306M
PGD307M
PGD308M
PGD310H
PGD310M
PGD311C
PGD311M
PGD312M
PGD317M
PGD319M
PGD31BM
PGD320M
PGD321M
PGD322M
PGD323A
PGD323M
PGD324M
PGD325C
PGD325M
PGD326M
PGD328M
PGD329C
PGD329M
PGD331M
PGD332C
PGD333C
PGD333M
PGD334M
PGD335M
PGD336C
PGD338M
PGD340A
PGD340M
PGD342M
PGD343R
PGD344M
PGD346M
PGD347R
PGD348M
PGD349C
PGD353M
PGD354M
PGD356C
PGD356M
PGD357M
PGD358M
PGD359M
PGD363C
PGD363M
PGD365M
PGD367M
PGD368M
PGD369C
PGD369M
PGD370C
PGD370M
PGD373M
PGD374C
PGD374M
PGD375M
PGD376C
PGD376M
PGD377M
PGD378M
PGD379M
PGD381M
PGD382M
PGD383C
PGD383M
PGD385M
PGD386M
PGD388M
PGD389C
PGD389M
PGD390M
PGD391M
PGD393M
PGD394M
PGD395M
PGD396M
PGD397M
PGD398M
PGD399C
PGD399M
PGD400M
PGD401M
PGD402M
PGD403M
PGD405M
PGD406M
PGD407M
PGD408M
PGD409C
PGD409M
PGD410C
PGD411M
PGD412M
PGD413M
PGD414M
PGD415M
PGD417M
PGD417R
PGD418C
PGD418M
PGD419M
PGD420M
PGD421M
PGD422C
PGD422M
PGD423M
PGD424M
PGD425M
PGD427M
PGD428M
PGD431M
PGD433A
PGD434M
PGD436A
PGD436C
PGD436M
PGD43AM
PGD440M
PGD450M
PGD451C
PGD451M
PGD453M
PGD454M
PGD456M
PGD458C
PGD458M
PGD459M
PGD460M
PGD461M
PGD462C
PGD462M
PGD465A
PGD465C
PGD465M
PGD466M
PGD468M
PGD470C
PGD470M
PGD471C
PGD471M
PGD472M
PGD473C
PGD473M
PGD475M
PGD476C
PGD476M
PGD477C
PGD477M
PGD478M
PGD479M
PGD480M
PGD481M
PGD482C
PGD484C
PGD484M
PGD486C
PGD486M
PGD489M
PGD491M
PGD492M
PGD493M
PGD496C
PGD496M
PGD498C
PGD498M
PGD499R
PGD500M
PGD501C
PGD502C
PGD502M
PGD503C
PGD503M
PGD505M
PGD506C
PGD506M
PGD507C
PGD507M
PGD508M
PGD509M
PGD510M
PGD517M
PGD518M
PGD519M
PGD520M
PGD522M
PGD523M
PGD524M
PGD525M
PGD527M
PGD529M
PGD52QS
PGD530C
PGD530M
PGD531C
PGD532C
PGD534C
PGD535M
PGD536C
PGD537C
PGD537M
PGD538M
PGD540C
PGD540M
PGD541M
PGD542M
PGD544R
PGD546M
PGD547M
PGD548M
PGD550M
PGD551C
PGD551M
PGD552M
PGD553C
PGD553M
PGD555M
PGD556C
PGD556M
PGD557M
PGD558M
PGD561M
PGD562C
PGD562M
PGD563C
PGD563M
PGD566M
PGD567C
PGD567M
PGD568C
PGD568M
PGD572C
PGD572M
PGD576C
PGD576M
PGD578M
PGD579C
PGD580A
PGD580C
PGD580M
PGD581M
PGD582M
PGD583C
PGD583M
PGD584C
PGD584M
PGD587C
PGD587M
PGD588C
PGD588M
PGD590M
PGD591C
PGD591M
PGD592M
PGD598C
PGD598M
PGD599R
PGD600C
PGD600M
PGD601C
PGD601M
PGD602C
PGD602M
PGD603M
PGD604C
PGD604M
PGD606C
PGD606M
PGD608M
PGD609M
PGD610C
PGD610R
PGD611C
PGD611M
PGD612C
PGD612M
PGD613C
PGD614M
PGD617C
PGD617M
PGD618M
PGD619C
PGD619M
PGD620M
PGD621C
PGD621M
PGD623C
PGD623M
PGD625M
PGD627C
PGD628M
PGD629M
PGD630M
PGD632C
PGD632M
PGD633M
PGD634M
PGD635C
PGD635M
PGD636C
PGD636M
PGD637C
PGD637M
PGD638M
PGD639M
PGD640M
PGD641M
PGD641R
PGD642M
PGD643C
PGD644M
PGD645M
PGD647M
PGD648M
PGD649M
PGD650C
PGD650M
PGD651M
PGD652M
PGD653C
PGD653M
PGD655M
PGD658R
PGD659M
PGD660M
PGD661M
PGD662M
PGD664M
PGD665C
PGD665M
PGD666C
PGD666M
PGD667M
PGD668C
PGD668M
PGD669C
PGD670M
PGD672C
PGD672M
PGD674A
PGD674M
PGD676M
PGD677C
PGD679C
PGD679M
PGD680C
PGD681M
PGD682M
PGD683M
PGD684M
PGD685C
PGD687M
PGD688M
PGD689C
PGD689M
PGD691C
PGD694M
PGD697C
PGD697M
PGD698C
PGD699C
PGD699M
PGD700C
PGD700M
PGD702C
PGD702M
PGD703M
PGD704M
PGD706M
PGD707C
PGD707M
PGD708M
PGD709A
PGD709M
PGD710M
PGD711C
PGD711M
PGD714C
PGD714M
PGD715M
PGD716C
PGD720M
PGD721C
PGD721M
PGD722C
PGD722M
PGD723C
PGD724C
PGD725M
PGD726C
PGD727C
PGD729C
PGD730A
PGD730C
PGD730M
PGD731C
PGD731M
PGD732C
PGD732M
PGD733M
PGD736M
PGD739M
PGD741C
PGD741M
PGD746M
PGD748C
PGD748M
PGD749C
PGD749M
PGD750C
PGD751M
PGD753M
PGD754M
PGD755C
PGD757M
PGD758M
PGD759M
PGD760M
PGD761M
PGD762M
PGD763M
PGD768M
PGD769M
PGD770C
PGD770M
PGD771C
PGD772C
PGD773C
PGD774M
PGD777M
PGD779M
PGD780M
PGD781M
PGD782M
PGD783M
PGD784C
PGD784M
PGD785C
PGD785M
PGD786M
PGD787C
PGD787M
PGD790C
PGD790M
PGD791M
PGD792C
PGD792M
PGD793C
PGD794M
PGD795M
PGD796M
PGD797M
PGD798C
PGD798M
PGD799C
PGD799M
PGD800M
PGD801M
PGD802M
PGD803M
PGD804C
PGD804M
PGD805M
PGD806C
PGD806M
PGD808M
PGD812C
PGD812M
PGD813C
PGD815C
PGD815M
PGD816C
PGD816M
PGD817C
PGD819M
PGD820M
PGD821M
PGD822C
PGD822M
PGD823C
PGD824M
PGD828C
PGD829C
PGD830C
PGD830M
PGD831C
PGD832C
PGD833C
PGD833M
PGD834C
PGD834M
PGD835C
PGD837M
PGD838M
PGD839M
PGD840M
PGD841M
PGD842M
PGD843C
PGD843M
PGD844M
PGD847M
PGD848M
PGD853M
PGD855C
PGD856M
PGD857M
PGD858M
PGD862C
PGD864C
PGD864M
PGD865C
PGD866C
PGD866M
PGD867C
PGD868C
PGD869M
PGD870C
PGD870M
PGD871C
PGD872M
PGD873M
PGD874M
PGD875M
PGD881C
PGD881M
PGD882C
PGD882M
PGD883C
PGD883M
PGD884C
PGD884M
PGD885C
PGD886M
PGD887C
PGD888C
PGD888M
PGD889C
PGD889M
PGD890C
PGD890M
PGD891M
PGD892C
PGD893C
PGD893M
PGD897M
PGD898M
PGD899M
PGD900C
PGD900M
PGD901M
PGD902M
PGD904M
PGD905C
PGD905M
PGD906C
PGD906M
PGD908C
PGD908M
PGD909C
PGD909M
PGD910M
PGD911M
PGD912M
PGD913C
PGD914C
PGD914M
PGD915M
PGD918M
PGD920M
PGD921C
PGD921M
PGD922C
PGD922M
PGD923C
PGD923M
PGD924C
PGD924M
PGD925C
PGD926M
PGD929C
PGD929M
PGD930C
PGD930M
PGD931C
PGD931M
PGD932C
PGD932M
PGD933M
PGD934M
PGD935M
PGD937M
PGD938M
PGD939M
PGD940M
PGD941C
PGD941M
PGD943C
PGD943M
PGD944C
PGD944M
PGD945M
PGD946M
PGD947M
PGD948M
PGD952M
PGD953C
PGD953M
PGD954C
PGD954M
PGD955C
PGD955M
PGD956C
PGD956M
PGD957C
PGD957M
PGD959C
PGD959M
PGD960M
PGD961M
PGD962M
PGD963M
PGD964C
PGD964M
PGD965M
PGD966M
PGD967M
PGD969C
PGD969M
PGD96QS
PGD970M
PGD972M
PGD973C
PGD973M
PGD974C
PGD974M
PGD976C
PGD976M
PGD978M
PGD979M
PGD980M
PGD981C
PGD981M
PGD982M
PGD985M
PGD986M
PGD988C
PGD988M
PGD989C
PGD989M
PGD992M
PGD995C
PGD995M
PGD996C
PGD996M
PGD997C
PGD997M
PGD998C
PGD998M
PGD999C
PGD999M
SGD101M
SGD120M
SGD130M
SGD131M
SGD135M
SGD136A
SGD136M
SGD137M
SGD142M
SGD145M
SGD147A
SGD147M
SGD149M
SGD150M
SGD151M
SGD152M
SGD153M
SGD154M
SGD158M
SGD170M
SGD179M
SGD183M
SGD188M
SGD189M
SGD191M
SGD195M
SGD199M
SGD200M
SGD202M
SGD203M
SGD207M
SGD214M
SGD215M
SGD218M
SGD219M
SGD220M
SGD221M
SGD227M
SGD228M
SGD240M
SGD242M
SGD245M
SGD249M
SGD255M
SGD256C
SGD257M
SGD258M
SGD259M
SGD261M
SGD262M
SGD263M
SGD264M
SGD266M
SGD269M
SGD270M
SGD272M
SGD273C
SGD274M
SGD275M
SGD276M
SGD278M
SGD279M
SGD280M
SGD281M
SGD282M
SGD284M
SGD289M
SGD290M
SGD291M
SGD294M
SGD295M
SGD296M
SGD297M
SGD299M
SGD303M
SGD306M
SGD307M
SGD308M
SGD310M
SGD311C
SGD317M
SGD319M
SGD31BM
SGD320M
SGD322M
SGD323M
SGD325C
SGD326M
SGD328M
SGD329C
SGD331C
SGD332C
SGD333M
SGD334M
SGD335M
SGD336C
SGD338M
SGD340M
SGD344M
SGD348M
SGD349C
SGD353M
SGD354M
SGD356M
SGD357M
SGD358M
SGD359M
SGD363C
SGD367M
SGD368M
SGD369M
SGD370M
SGD373M
SGD374C
SGD375M
SGD376M
SGD377M
SGD378M
SGD381M
SGD382M
SGD383C
SGD385M
SGD386M
SGD388M
SGD389C
SGD390M
SGD391M
SGD393M
SGD394M
SGD395M
SGD396M
SGD397M
SGD399M
SGD401M
SGD402M
SGD403M
SGD409M
SGD410C
SGD412M
SGD413M
SGD414M
SGD415M
SGD417M
SGD418C
SGD419M
SGD420M
SGD421M
SGD422M
SGD423M
SGD424M
SGD427M
SGD428M
SGD431M
SGD434M
SGD436C
SGD43AM
SGD440M
SGD450M
SGD451M
SGD456M
SGD458C
SGD459M
SGD460M
SGD461M
SGD462C
SGD465C
SGD466M
SGD470M
SGD471M
SGD473M
SGD475M
SGD476C
SGD477M
SGD478M
SGD479M
SGD480M
SGD481M
SGD482C
SGD484C
SGD486M
SGD489M
SGD491M
SGD492M
SGD493M
SGD496C
SGD498M
SGD500M
SGD501C
SGD502M
SGD503C
SGD505M
SGD506M
SGD507C
SGD508M
SGD509M
SGD510M
SGD511C
SGD517M
SGD522M
SGD523M
SGD524M
SGD525M
SGD527C
SGD528C
SGD529M
SGD530C
SGD531C
SGD532C
SGD534C
SGD535M
SGD536C
SGD537C
SGD538M
SGD540C
SGD541M
SGD542M
SGD549C
SGD550M
SGD551C
SGD552C
SGD553C
SGD555M
SGD556C
SGD557M
SGD558M
SGD561M
SGD562C
SGD563C
SGD566M
SGD567C
SGD568C
SGD571C
SGD572C
SGD576C
SGD579C
SGD580C
SGD582M
SGD583C
SGD584C
SGD587C
SGD588C
SGD598C
SGD600C
SGD601C
SGD601M
SGD602C
SGD603M
SGD604C
SGD606C
SGD610C
SGD611C
SGD612C
SGD613C
SGD614M
SGD617C
SGD618M
SGD619C
SGD621C
SGD623M
SGD625M
SGD627C
SGD628M
SGD629M
SGD630M
SGD631C
SGD632M
SGD633M
SGD635C
SGD636C
SGD637C
SGD641M
SGD642M
SGD643C
SGD644M
SGD645M
SGD647M
SGD648M
SGD649M
SGD650M
SGD652M
SGD653C
SGD655M
SGD659M
SGD660M
SGD661C
SGD662M
SGD664M
SGD665C
SGD666C
SGD667M
SGD668C
SGD669C
SGD670M
SGD672C
SGD674M
SGD676M
SGD677C
SGD679M
SGD680C
SGD684M
SGD685C
SGD687M
SGD688M
SGD689M
SGD691C
SGD694M
SGD697C
SGD698C
SGD699M
SGD700M
SGD702M
SGD704M
SGD707C
SGD708M
SGD709M
SGD710M
SGD711M
SGD714M
SGD720M
SGD721C
SGD722C
SGD723C
SGD724C
SGD725M
SGD726C
SGD727C
SGD729C
SGD730M
SGD733C
SGD736M
SGD741C
SGD746M
SGD748M
SGD749M
SGD750C
SGD751M
SGD753M
SGD754M
SGD755C
SGD757M
SGD758M
SGD760M
SGD762M
SGD763M
SGD768M
SGD770C
SGD771C
SGD772C
SGD773C
SGD774M
SGD775C
SGD779M
SGD780M
SGD781M
SGD782M
SGD783M
SGD784C
SGD785C
SGD787C
SGD790M
SGD791M
SGD792C
SGD793C
SGD794M
SGD795M
SGD796M
SGD797M
SGD798C
SGD799C
SGD802M
SGD803M
SGD804C
SGD805M
SGD806C
SGD812C
SGD813C
SGD815C
SGD816C
SGD817C
SGD819M
SGD820M
SGD821M
SGD822C
SGD823C
SGD824M
SGD828C
SGD830M
SGD831C
SGD832C
SGD833M
SGD834C
SGD835C
SGD837M
SGD838M
SGD840M
SGD841M
SGD842M
SGD843M
SGD844M
SGD855C
SGD856M
SGD857M
SGD858M
SGD862C
SGD864C
SGD865C
SGD866C
SGD867C
SGD868C
SGD869M
SGD870C
SGD871C
SGD881C
SGD882C
SGD883C
SGD884C
SGD885C
SGD886M
SGD889C
SGD892C
SGD893C
SGD898M
SGD899M
SGD900C
SGD902M
SGD904C
SGD905C
SGD906C
SGD908C
SGD909C
SGD913C
SGD914C
SGD923C
SGD924C
SGD925C
SGD926M
SGD929C
SGD931M
SGD932C
SGD934M
SGD935M
SGD937M
SGD941C
SGD945M
SGD946M
SGD948C
SGD956C
SGD959C
SGD965M
SGD966M
SGD976M
ATD1000C
ATD1005C
ATD1006M
ATD1008C
ATD1010M
ATD1015C
ATD1019C
ATD1022C
ATD1028C
ATD1032M
ATD1033C
ATD1038C
ATD1039C
ATD1040C
ATD1041C
ATD1045M
ATD1046M
ATD1048C
ATD1056C
ATD1057C
ATD1058C
ATD1058P
ATD1064M
ATD1066M
ATD1067M
ATD1068M
ATD1069M
ATD1070C
ATD1073C
ATD1075C
ATD1080C
ATD1088C
ATD1089C
ATD1092C
ATD1093C
ATD1094C
ATD1100C
ATD1101C
ATD1105C
ATD1113C
ATD1114C
ATD1119C
ATD1120C
ATD1124C
ATD1136M
ATD1159C
ATD1159P
ATD1161C
ATD1164C
ATD1185M
ATD1192C
ATD1194C
ATD1222C
ATD433AC
ATD433BM
ATD436AC
ATD465AC
ATD580AC
ATD655AM
ATD667AC
ATD674AC
ATD674AM
ATD674AP
ATD702AM
ATD730AC
ATD731AC
ATD786AM
ATD855AC
ATD888AC
ATD906AC
ATD974AC
ATD974AP
PGD1000M
PGD1001M
PGD1002C
PGD1002M
PGD1003M
PGD1004C
PGD1005C
PGD1005M
PGD1007M
PGD1008C
PGD1008M
PGD1009M
PGD1010M
PGD1011M
PGD1012M
PGD1013M
PGD1014M
PGD1015C
PGD1016M
PGD1017M
PGD1018M
PGD1019M
PGD1020C
PGD1021C
PGD1021M
PGD1022C
PGD1022M
PGD1024M
PGD1028C
PGD1028M
PGD1030C
PGD1031C
PGD1033C
PGD1035C
PGD1036C
PGD1037C
PGD1038C
PGD1039C
PGD1040C
PGD1041C
PGD1042M
PGD1043C
PGD1044C
PGD1045M
PGD1046M
PGD1047C
PGD1048C
PGD1049M
PGD1050M
PGD1051C
PGD1053M
PGD1054M
PGD1055C
PGD1056C
PGD1057C
PGD1058C
PGD1059M
PGD1060M
PGD1061M
PGD1064M
PGD1065M
PGD1066M
PGD1067M
PGD1068M
PGD1069M
PGD1070C
PGD1071C
PGD1073C
PGD1074C
PGD1075C
PGD1077M
PGD1078C
PGD1080C
PGD1081C
PGD1082C
PGD1083M
PGD1084M
PGD1085M
PGD1086C
PGD1088C
PGD1089C
PGD1090C
PGD1091C
PGD1092C
PGD1093C
PGD1094C
PGD1095C
PGD1095M
PGD1097C
PGD1100C
PGD1101C
PGD1102C
PGD1103C
PGD1104C
PGD1105C
PGD1107C
PGD1107M
PGD1108C
PGD1108M
PGD1111M
PGD1112M
PGD1113C
PGD1114C
PGD1118M
PGD1119C
PGD1120C
PGD1121M
PGD1123M
PGD1124C
PGD1125C
PGD1130M
PGD1149M
PGD1156C
PGD1157C
PGD1158C
PGD1159C
PGD1160C
PGD1161C
PGD1164C
PGD1169C
PGD1170M
PGD1171M
PGD1172C
PGD1178M
PGD1179C
PGD1184C
PGD1188C
PGD1192C
PGD1194C
PGD1195C
PGD1202C
PGD1210C
PGD1211C
PGD1212C
PGD1222C
PGD1258C
PGD1259C
PGD145AM
PGD149QS
PGD154QS
PGD158AM
PGD227AM
PGD233AM
PGD242QS
PGD249QS
PGD269QS
PGD273QS
PGD280BM
PGD280CM
PGD280DM
PGD323AC
PGD325QS
PGD333QS
PGD334QS
PGD340AC
PGD340AM
PGD344AM
PGD349QS
PGD368QS
PGD369QS
PGD370QS
PGD374QS
PGD375QS
PGD376QS
PGD377QS
PGD383QS
PGD391QS
PGD394AM
PGD394QS
PGD396QS
PGD407AM
PGD408AM
PGD409QS
PGD412QS
PGD413QS
PGD419AM
PGD421AM
PGD421QS
PGD433AC
PGD436AC
PGD436AM
PGD459QS
PGD465AC
PGD465AM
PGD465QS
PGD466AM
PGD470QS
PGD471QS
PGD473QS
PGD476QS
PGD477QS
PGD484QS
PGD486QS
PGD491AM
PGD492QS
PGD493AM
PGD493QS
PGD499AM
PGD499AR
PGD502QS
PGD503QS
PGD505QS
PGD506QS
PGD507QS
PGD508QS
PGD518AM
PGD520AM
PGD522QS
PGD524QS
PGD526QS
PGD529AM
PGD530QS
PGD536QS
PGD537QS
PGD540QS
PGD542QS
PGD551QS
PGD555AM
PGD555BM
PGD558QS
PGD561AM
PGD562QS
PGD567QS
PGD576QS
PGD579QS
PGD580AC
PGD580QS
PGD587QS
PGD588QS
PGD591QS
PGD598QS
PGD600QS
PGD601QS
PGD610QS
PGD611QS
PGD613QS
PGD614AM
PGD617QS
PGD618QS
PGD619QS
PGD623QS
PGD627QS
PGD632QS
PGD637QS
PGD638AM
PGD640QS
PGD642QS
PGD643QS
PGD648QS
PGD650QS
PGD652QS
PGD653AC
PGD653AM
PGD653QS
PGD655AM
PGD659QS
PGD665QS
PGD666QS
PGD667AM
PGD667QS
PGD668QS
PGD672QS
PGD674AM
PGD674QS
PGD679QS
PGD684AM
PGD687AM
PGD688AM
PGD691QS
PGD697QS
PGD698QS
PGD699QS
PGD702AM
PGD702QS
PGD707QS
PGD709AC
PGD709AM
PGD709QS
PGD711QS
PGD714QS
PGD720QS
PGD721QS
PGD722QS
PGD723QS
PGD726QS
PGD727QS
PGD729QS
PGD730AC
PGD730AM
PGD730QS
PGD731QS
PGD732AM
PGD732QS
PGD737QS
PGD741QS
PGD746QS
PGD748QS
PGD749QS
PGD750QS
PGD753QS
PGD754QS
PGD755QS
PGD757QS
PGD758QS
PGD760QS
PGD762AM
PGD768AM
PGD768QS
PGD771QS
PGD774QS
PGD784AC
PGD784AM
PGD784QS
PGD785QS
PGD786AM
PGD787QS
PGD790QS
PGD791QS
PGD792QS
PGD793QS
PGD798QS
PGD799QS
PGD803QS
PGD804QS
PGD805QS
PGD806QS
PGD812QS
PGD815AC
PGD815AM
PGD815QS
PGD816QS
PGD817QS
PGD820QS
PGD822QS
PGD823QS
PGD824QS
PGD828QS
PGD829QS
PGD830QS
PGD833AM
PGD833QS
PGD834QS
PGD835QS
PGD839AM
PGD840AM
PGD841QS
PGD843QS
PGD853AM
PGD855AC
PGD855QS
PGD856AC
PGD856AM
PGD856QS
PGD862QS
PGD864AC
PGD864AM
PGD865QS
PGD866QS
PGD867QS
PGD868QS
PGD881QS
PGD882QS
PGD883QS
PGD884QS
PGD888AC
PGD899AM
PGD905QS
PGD906AC
PGD908QS
PGD909QS
PGD913QS
PGD914QS
PGD921QS
PGD922QS
PGD923QS
PGD924QS
PGD931QS
PGD932QS
PGD934QS
PGD935QS
PGD943QS
PGD946AM
PGD959QS
PGD966AM
PGD969QS
PGD974AC
PGD974AM
PGD974QS
PGD976QS
PGD981QS
PGD999QS
SGD1013C
SGD1028C
SGD1030C
SGD1031C
SGD1033C
SGD1035C
SGD1036C
SGD1039C
SGD1044C
SGD1047C
SGD1074C
SGD145AM
SGD158AM
SGD227AM
SGD233AM
SGD280BM
SGD280DM
SGD323AC
SGD340AM
SGD344AM
SGD421AM
SGD433AC
SGD436AC
SGD465AC
SGD466AM
SGD499AM
SGD518AM
SGD520AM
SGD529AM
SGD555AM
SGD580AC
SGD614AM
SGD655AM
SGD667AM
SGD674AM
SGD687AM
SGD688AM
SGD702AM
SGD709AM
SGD730AM
SGD762AM
SGD768AM
SGD815AC
SGD856AC
SGD864AC
SGD906AC
ATD1019AC
ATD1040AC
ATD1040AP
ATD1057AP
ATD1185AM
PGD1000QS
PGD1005QS
PGD1008QS
PGD1009QS
PGD1013QS
PGD1015QS
PGD1019AM
PGD1019QS
PGD1020AM
PGD1020QS
PGD1021QS
PGD1022QS
PGD1028QS
PGD1033QS
PGD1038QS
PGD1039QS
PGD1040AC
PGD1040QS
PGD1041QS
PGD1043QS
PGD1047QS
PGD1048QS
PGD1051QS
PGD1055QS
PGD1056QS
PGD1057AM
PGD1057QS
PGD1058QS
PGD1070QS
PGD1073QS
PGD1075QS
PGD1078QS
PGD1080QS
PGD1081QS
PGD1082QS
PGD1086QS
PGD1088QS
PGD1089QS
PGD1090QS
PGD1091QS
PGD1092QS
PGD1093QS
PGD1094QS
PGD1100QS
PGD1101QS
PGD1102QS
PGD1103QS
PGD1105QS
PGD1113QS
PGD1114QS
PGD1119QS
PGD1120QS
PGD1124QS
PGD1158QS
PGD1159QS
PGD1161QS
PGD1164QS
PGD1169QS
PGD1172QS
PGD1194QS
PGD421AQS
PGD433AQS
PGD436AQS
PGD465AQS
PGD580AQS
PGD653AQS
PGD667AQS
PGD674AQS
PGD730AQS
PGD731AQS
PGD784AQS
PGD815AQS
PGD855AQS
PGD856AQS
PGD906AQS
PGD974AQS
PGD1019AQS
PGD1040AQS

okay, hopefully i understood your goal better this time. paste the following
SQL statement into a new query's SQL pane, as

SELECT IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA,
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0) AS SortB, Table13.[Comp
Part]
FROM Table13
ORDER BY IIf(Asc(Left([Comp Part],1))<58,0,Len(IIf(InStr(1,[Comp
Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))),
IIf(Asc(Left([Comp Part],1))<65,CLng([Comp Part]),0), Table13.[Comp Part];

remember to replace Table13 with the correct table name, of course. if the
sort order fits your needs, then include the two calculated fields in the
"real" query that the report is based on. in the *report*, sort the data by
fields SortA, SortB, and [Comp Part], in that order. (note: don't bother
sorting the fields at the query level at all, it's the report level sort
that will "stick".)

hth

Thanks for the reply.

The sort I want is numeric first based on the number of places, i.e. 2
digits in order, then 3 digits in order, then 4 digits in order, etc all
the way to 6 digits in order, each group in numberical order, not computer
order.

Then, the sort should deal with strings that begin with Alphas. Sort on
the Alpha letters first and then the numbers following the alpha, i.e.
Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6
digits. What I am after is very close to the example I offered although I
think I got some out of actual order. Some of the Alpha 5 digit have a
hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit.

Also, I was planning to put the sorting formula in a Report, probably in
the Sorting and Grouping area. BTW, the Field I want to sort is named
"Comp Part" if that helps!

Thanks

I am looking for a way to sort our numeric numbers based on length and
then
sort our Alpha/numeric parts up the hyphen in the part number

i took the list of values you posted and dumped them into a single-field
table, then created a query on that table, as

SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1))
AS SortBy
FROM Table13;

the [x] field is the values, the SortBy field is a calculated field that
returns the [x] value when there's no hyphen, and the [x] value up to the
hyphen on all others. but what i'm seeing is that sorting by the SortBy
field returns the same order as sorting by the [x] field, so there's no need
to "get rid of" the hyphenated suffix before sorting.

can you post an example of how you *want* the previously posted list to be
sorted?

hth


Yes, we sort our numbers before our alpha numbers, but we do it based on
length within those groups, i.e.

1234
12345
123456

ABS12345
ABS123456
ATD123
ATD123C
ATD123M
ATD1234
ATD1234C
ATD1234M
BC1234
BC12345
BC123456
BH1234
BH12345
BH123456
CMA12345
CMA123456
H12
H12-2
H123
H123K
H123W
H123-2
H1234
H1234-2
H1234K
H1234W
H12345
H12345-2
H12345K
H12345W
MC1234
MC12345
MC123456
PGD123
PGD123C
PGD123M
PGD1234C
PGD1234M
SC12345
SC123456
SGD123
SGD123C
SGD123M
SGD1234
SGD1234C
SGD1234M
WC1234
WC12345
WC123456

There are more Alpha prefixes, but if I can sort this sample, the rest
will
fall into place as well.

Thanks!


On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce"

Tom

Without an example to help "see" what you're doing, this is only a
guess...

It sounds like you want to sort your "part numbers" in alphabetic order.
If
any of the part numbers start with numbers, those should sort before the
alpha-beginning numbers.

 

[Chuck]

Am running an Access 2K database in Access 2003. Have not converted it yet
and cannot for awhile until entire sales force has the new Laptops running
Access 2003.

My application deals with some 24,000+ part numbers that sometimes need to
be sorted in our part number sequence in Reports. Our part numbers involve
both numeric only number consisting of string lengths from 4 to 8 AND part
number that begin with Alpha characters (1 or 2 or 3 Alphas) then numeric
numbers with string lengths of 3 to 8. Some of these numbers have a hyphen
in them that skew the actual string length for sorting based on length. We
publish our price lists and put our products on the shelf based on the
length of the part number (up to the hyphen), then the sort.

I am looking for a way to sort our numeric numbers based on length and then
sort our Alpha/numeric parts up the hyphen in the part number when the
Report is printed.

If someone could point me in correct direction, it would be much
appreciated!

TIA!

This is not an Access or VB problem per se.
This is a logic problem. First solve the logic of what you are trying to do.
Express the logic in a series of plain english statements (or what ever your
first language is), or a series of algebraic equations.
The logic does not change from one program to another, only the syntax the
program requires varies - in this case you need the syntax Access requires.

Example: In VB you might have if ... then ... else ...
In Access you would have iif ... then ... else ...
In Excel you would have if(... , ... , ...)
The syntax is different but the results are the same.

Chuck --

 

[Tom]

Thanks for the reply!

This is not an Access or VB problem per se.
This is a logic problem. First solve the logic of what you are trying to do.
Express the logic in a series of plain english statements (or what ever your
first language is), or a series of algebraic equations.
The logic does not change from one program to another, only the syntax the
program requires varies - in this case you need the syntax Access requires.

Example: In VB you might have if ... then ... else ...
In Access you would have iif ... then ... else ...
In Excel you would have if(... , ... , ...)
The syntax is different but the results are the same.

Chuck --

 

[Marshall Barton]

Here's an idea that might be easier to deal with. Create a
UDF to translate your part numbers into a standard, sortable
string. First, pad the initial alphas with spaces to make
it a fixed length of say 5 (to allow for possible future
longer alpha parts). Next, pad the numeric portion with
leading 0s. Finally, add the trailing alphas. This
function can then be used in the report's Sorting and
Grouping.

Because your explanations and examples are inconsistent, I
am only making a wild stab at some of the code in the
function:

Public Function StandardizePartNum(PartNum)
Dim k As Integer, j As Integer

If IsNull(PartNum) Then
StandardizePartNum = Null
Exit Function
End If

'find first digit
For k = 1 To Len(PartNum)
If Mid(PartNum, k, 1) Like "#" Then Exit For
Next k
'find next non-numeric character
For j = k + 1 To Len(PartNum)
If Not Mid(PartNum, j, 1) Like "#" Then Exit For
Next j
StandardizePartNum = _
Left(PartNum, k - 1) & Space(6 - k) _
& Format(Val(Mid(PartNum, k)), "0000000") _
& Mid(PartNum, j)
End Function

 

[Tom]

Thanks for the idea. I have thought about creating a file to solve this
issue, but never have. Let me give this some thought.

 

[Tom]

Marshall,

Pls explain or point me to an explanation of a UDF. Thx!

Tom

 

[John Spencer]

UDF = User defined function

In this case a function written in VBA as Marshall did.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

Pls explain or point me to an explanation of a UDF. Thx!

Just open a new module (using the database window) and
Copy/Paste the code into the module. Since it is Public,
you can call it from anywhere in Access. E.g. in the
report's Sorting and Grouping Field/Expression:
=StandardizePartNum([Comp Part])

Or, in an empty field in a query:
Field Expr1: StandardizePartNum([Comp Part])
Sort Ascending
Show unchecked (after debugging)

 

[Tom]

Thanks!

Pls explain or point me to an explanation of a UDF. Thx!

Just open a new module (using the database window) and
Copy/Paste the code into the module. Since it is Public,
you can call it from anywhere in Access. E.g. in the
report's Sorting and Grouping Field/Expression:
=StandardizePartNum([Comp Part])

Or, in an empty field in a query:
Field Expr1: StandardizePartNum([Comp Part])
Sort Ascending
Show unchecked (after debugging)