?
Solved

Part Number Alpha Numeric Sorting - Access 2003 Query

Posted on 2008-06-17
9
Medium Priority
?
825 Views
Last Modified: 2013-11-27
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.)
0
Comment
Question by:shrimpfork
  • 5
  • 4
9 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 21802988
Hello shrimpfork,

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


Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True) 
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841 
    ' 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 Answer() As String
    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)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
        
        ' 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

Open in new window

0
 

Author Comment

by:shrimpfork
ID: 21803360
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 21804004
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:shrimpfork
ID: 21804096
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 21804549
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) 
 
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841 
 
    ' 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 Answer() As String
    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)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
        
        ' 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

Open in new window

0
 

Author Comment

by:shrimpfork
ID: 21804962
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 21805409
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 21805493
shrimpfork,

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

Regards,

Patrick
0
 

Author Closing Comment

by:shrimpfork
ID: 31467949
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question