Solved

Part Number Alpha Numeric Sorting - Access 2003 Query

Posted on 2008-06-17
9
777 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 92

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 92

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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 125 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 92

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 92

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now