?
Solved

Part Number Alpha Numeric Sorting - Access 2003 Query

Posted on 2008-06-17
9
Medium Priority
?
811 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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