# 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.)
###### Who is Participating?

x

Commented:
The code below has not been tested, but if it works as I expect it to then it should improve things a little
bit by persisting the RegExp object.

Regards,

Patrick

``````Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
Optional MatchCase As Boolean = True)

' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
' pattern (PatternStr).  Use Pos to indicate which match you want:
' Pos omitted               : function returns a zero-based array of all matches
' Pos = 0                   : the last match
' Pos = 1                   : the first match
' Pos = 2                   : the second match
' Pos = <positive integer>  : the Nth match

' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
' returns an empty string.  If no match is found, the function returns an empty string

' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).

' If you use this function in Excel, you can use range references for any of the arguments.
' If you use this in Excel and return the full array, make sure to set up the formula as an
' array formula.  If you need the array formula to go down a column, use TRANSPOSE()

Static RegX As Object
Dim TheMatches As Object
Dim Counter As Long

' Evaluate Pos.  If it is there, it must be numeric and converted to Long
If Not IsMissing(Pos) Then
If Not IsNumeric(Pos) Then
RegExpFind = ""
Exit Function
Else
Pos = CLng(Pos)
End If
End If

' Create instance of RegExp object
If RegX Is Nothing Then
Set RegX = CreateObject("VBScript.RegExp")
End If

With RegX
.Pattern = PatternStr
.Global = True
.IgnoreCase = Not MatchCase
End With

' Test to see if there are any matches
If RegX.test(LookIn) Then

' Run RegExp to get the matches, which are returned as a zero-based collection
Set TheMatches = RegX.Execute(LookIn)

' If Pos is missing, user wants array of all matches.  Build it and assign it as the
' function's return value
If IsMissing(Pos) Then
ReDim Answer(0 To TheMatches.Count - 1) As String
For Counter = 0 To UBound(Answer)
Next

' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
Else
Select Case Pos
Case 0                          ' Last match
RegExpFind = TheMatches(TheMatches.Count - 1)
Case 1 To TheMatches.Count      ' Nth match
RegExpFind = TheMatches(Pos - 1)
Case Else                       ' Invalid item number
RegExpFind = ""
End Select
End If

' If there are no matches, return empty string
Else
RegExpFind = ""
End If

' Release object variables
Set RegX = Nothing
Set TheMatches = Nothing

End Function
``````
0

Commented:
Hello shrimpfork,

Add the UDF In the snippet below to a regular VBA module, and then add an ORDER BY clause like this to

ORDER BY Val(RegExpFind(PartNum, "^\d+", 1)), RegExpFind(PartNum, "[A-Z]+", 1, False),
Val(RegExpFind(PartNum, "\d+\$", 1))

Regards,

Patrick

``````Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
Optional MatchCase As Boolean = True)
' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
' pattern (PatternStr).  Use Pos to indicate which match you want:
' Pos omitted               : function returns a zero-based array of all matches
' Pos = 0                   : the last match
' Pos = 1                   : the first match
' Pos = 2                   : the second match
' Pos = <positive integer>  : the Nth match
' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
' returns an empty string.  If no match is found, the function returns an empty string

' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).

' If you use this function in Excel, you can use range references for any of the arguments.
' If you use this in Excel and return the full array, make sure to set up the formula as an
' array formula.  If you need the array formula to go down a column, use TRANSPOSE()

Dim RegX As Object
Dim TheMatches As Object
Dim Counter As Long

' Evaluate Pos.  If it is there, it must be numeric and converted to Long
If Not IsMissing(Pos) Then
If Not IsNumeric(Pos) Then
RegExpFind = ""
Exit Function
Else
Pos = CLng(Pos)
End If
End If

' Create instance of RegExp object
Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Pattern = PatternStr
.Global = True
.IgnoreCase = Not MatchCase
End With

' Test to see if there are any matches
If RegX.test(LookIn) Then

' Run RegExp to get the matches, which are returned as a zero-based collection
Set TheMatches = RegX.Execute(LookIn)

' If Pos is missing, user wants array of all matches.  Build it and assign it as the
' function's return value
If IsMissing(Pos) Then
ReDim Answer(0 To TheMatches.Count - 1) As String
For Counter = 0 To UBound(Answer)
Next

' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
Else
Select Case Pos
Case 0                          ' Last match
RegExpFind = TheMatches(TheMatches.Count - 1)
Case 1 To TheMatches.Count      ' Nth match
RegExpFind = TheMatches(Pos - 1)
Case Else                       ' Invalid item number
RegExpFind = ""
End Select
End If

' If there are no matches, return empty string
Else
RegExpFind = ""
End If

' Release object variables
Set RegX = Nothing
Set TheMatches = Nothing

End Function
``````
0

Author Commented:
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.)
0

Commented:
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
0

Author Commented:
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.
0

Author Commented:
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?
0

Commented:
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
0

Commented:
shrimpfork,

BTW, I am curious to see whether my revised code ran any faster...

Regards,

Patrick
0

Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.