shrimpfork
asked on
Part Number Alpha Numeric Sorting - Access 2003 Query
Within a query in Access 2003, I am trying to sort a list of part numbers in the following format: NumericAlphaNumeric. Using Access general sorting it is listing the list as shown...
0101A1
0101A10
0101A11
0101A9
0101B1
0101B10
0101B2
0101BB1
0101BB2
11AA3
11AA34
11AA35
11AA4
12D1
12D10
12D102
12D103
And I would like to sort as the following...
11AA3
11AA4
11AA34
11AA35
12D1
12D10
12D102
12D103
0101A1
0101A9
0101A10
0101A11
0101B1
0101B2
0101B10
0101BB1
0101BB2
Where the list is sorted by the first Numeric, then by the Alpha and then finally by the last Numeric. I have started by creating an initial numeric "SORT" column within the query using Val() function. (I think I need a couple more sort columns, but I can not figure out how to extract the remaining Alpha and Numeric strings to create 2 more sort columns.) How do I sort the remaining AlphaNumeric of the part number within the query?
(Note that the Alpha portion of the part number is not constant in length.)
0101A1
0101A10
0101A11
0101A9
0101B1
0101B10
0101B2
0101BB1
0101BB2
11AA3
11AA34
11AA35
11AA4
12D1
12D10
12D102
12D103
And I would like to sort as the following...
11AA3
11AA4
11AA34
11AA35
12D1
12D10
12D102
12D103
0101A1
0101A9
0101A10
0101A11
0101B1
0101B2
0101B10
0101BB1
0101BB2
Where the list is sorted by the first Numeric, then by the Alpha and then finally by the last Numeric. I have started by creating an initial numeric "SORT" column within the query using Val() function. (I think I need a couple more sort columns, but I can not figure out how to extract the remaining Alpha and Numeric strings to create 2 more sort columns.) How do I sort the remaining AlphaNumeric of the part number within the query?
(Note that the Alpha portion of the part number is not constant in length.)
ASKER
Patrick,
Great code. It sorted my part numbers correctly, but it did SLOW down the query greatly. Is there a differant way to do this without calling VBA to run a code for every record? My list of part numbers is HUGE. (I am using this query to sort my part numbers for use in several forms and reports.)
Great code. It sorted my part numbers correctly, but it did SLOW down the query greatly. Is there a differant way to do this without calling VBA to run a code for every record? My list of part numbers is HUGE. (I am using this query to sort my part numbers for use in several forms and reports.)
shrimpfork,
There might be some finetuning on the code to make it run more efficiently (mainly by persisting the
RegExp object, and not continuously creating and destroying it), but it will still be painful to run.
That said, as long as your numeric and alpha lengths are inconsistent, I am not sure that a better
approach is within reach.
Regards,
Patrick
There might be some finetuning on the code to make it run more efficiently (mainly by persisting the
RegExp object, and not continuously creating and destroying it), but it will still be painful to run.
That said, as long as your numeric and alpha lengths are inconsistent, I am not sure that a better
approach is within reach.
Regards,
Patrick
ASKER
Patrick,
I'll have to look at it to see if I can improve the code. Without modifications, I applied this to my large list and it took 7-10 minutes to run the query. My users will kill me! I was hoping for a way to do a "reverse" VAL() from the right side of a string.
I'll have to look at it to see if I can improve the code. Without modifications, I applied this to my large list and it took 7-10 minutes to run the query. My users will kill me! I was hoping for a way to do a "reverse" VAL() from the right side of a string.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can see how this helped a little on my small list. But for my master part list I need a quicker solution. Right now it is taking in excessive of 3 minutes (I did not let it finish) to run the query.
If I can extract the last numeric portion of the string, using something like VAL() from the right side, I'll be golden. Do you have any thoughts how to do this?
If I can extract the last numeric portion of the string, using something like VAL() from the right side, I'll be golden. Do you have any thoughts how to do this?
shrimpfork,
If the numeric/alpha/numeric portions were of a fixed length, or had some sort of delimiter like a hyphen
or period, we could use all native functions, and it would go faster. The only other thing I can think of
is to break up the part number column into three separate columns.
Regards,
Patrick
If the numeric/alpha/numeric portions were of a fixed length, or had some sort of delimiter like a hyphen
or period, we could use all native functions, and it would go faster. The only other thing I can think of
is to break up the part number column into three separate columns.
Regards,
Patrick
shrimpfork,
BTW, I am curious to see whether my revised code ran any faster...
Regards,
Patrick
BTW, I am curious to see whether my revised code ran any faster...
Regards,
Patrick
ASKER
It actually did shave a minute or so off of the query. However for my initial test, I only used the alpha portion for the sort field, which took 5 minutes or so to run. If I remove the sort criteria the query runs fast and shows the separated part number string in the 3 sort fields. As soon as I apply ascending to any of the sort field, it seems to loop forever.
I'm going to resubmit this as question for the "Reverse Val()" to see if anybody knows how to do a VAL() starting from the right of a string.
Thanks for your input. I'm awarding the points.
I'm going to resubmit this as question for the "Reverse Val()" to see if anybody knows how to do a VAL() starting from the right of a string.
Thanks for your input. I'm awarding the points.
Add the UDF In the snippet below to a regular VBA module, and then add an ORDER BY clause like this to
your query:
ORDER BY Val(RegExpFind(PartNum, "^\d+", 1)), RegExpFind(PartNum, "[A-Z]+", 1, False),
Val(RegExpFind(PartNum, "\d+$", 1))
Regards,
Patrick
Open in new window