Search code examples
vbams-accessquicksort

Sort Alphanumeric Array Access-VBA

EDIT: Query is called from vba, used to create array, passed to create form:

QueryFieldAsSeparatedString("Batch", "TLR", "Batch")  
strBndls = Replace(strBndls, ",", ";")
subNewControls strBndls, "frmSelectBundle"

The reason I thought this quicksort would work is because batch #13 gets lumped in with the 1's using the sortby clause.

Example table:

  • Order # | Batch
  • 1 | 1
  • 1 | 13
  • 1 | 1b
  • 1 | 2
  • 1 | 2a
  • 1 | 1a
  • 1 | 3
  • 1 | 6
  • 1 | 7
  • 1 | 9
  • 1 | 11
  • 1 | 6a
  • 1 | 7b
  • 1 | 8
  • 1 | 13a
  • 1 | 7a

desired string returned: 1, 1a, 1b, 2, 2a, 3, 6, 6a, 7, 7a, 7b, 8, 9, 11, 13, 13a

Current Output Side Note: there are duplicates in this table that have different information separating them (shipment_box# and product_size). I'm hoping to group them since the operator would be entering data against the whole batch on the same order and child batch (batches with a letter), regardless of other data

Original Post:

I am attempting to sort a column in an access table. I found this code from VBA array sort function? and am attempting to make it work. I would have commented on the post but am unable to comment since I am new to the forum.

Specifically I am looking at this bit of code:

Public Sub QuickSort(ByRef Field() As String, ByVal LB As Long, ByVal UB As Long)
Dim P1 As Long, P2 As Long, Ref As String, TEMP As String

P1 = LB
P2 = UB
Ref = Field((P1 + P2) / 2)

Do
    Do While (Field(P1) < Ref)
        P1 = P1 + 1
    Loop

    Do While (Field(P2) > Ref)
        P2 = P2 - 1
    Loop

    If P1 <= P2 Then
        TEMP = Field(P1)
        Field(P1) = Field(P2)
        Field(P2) = TEMP

        P1 = P1 + 1
        P2 = P2 - 1
    End If
Loop Until (P1 > P2)

If LB < P2 Then Call QuickSort(Field, LB, P2)
If P1 < UB Then Call QuickSort(Field, P1, UB)
End Sub

I use this block of code to create a delimited string of all records in a 'Product Batch' field.

Public Function QueryFieldAsSeparatedString(ByVal fieldName As String, _
                                        ByVal tableOrQueryName As String, _
                                        Optional ByVal criteria As String = "", _
                                        Optional ByVal sortBy As String = "", _
                                        Optional ByVal delimiter As String = ", " _
                                    ) As String

' Paramter description
'   fieldName           =   Is the name of the field containing the values
'                           we want in our comma separated string
'   tableOrQueryName    =   Is the name of table or query containing the column
'   criteria            =   The criteria to filter the data
'   sortBy              =   An optional sort expression to sort the data
'   delimiter           =   The delimiter used to separate the values. It defaults
'                           to a comma and a blank, but you can use anything you
'                           like there 


Dim db              As DAO.Database
Dim rs              As DAO.Recordset
Dim sql             As String
Dim whereCondition  As String
Dim sortExpression  As String
Dim retVal          As String

Set db = CurrentDb

' If there where any criteria passed to the function, we build a WHERE-condition for SQL
If Len(criteria) > 0 Then
    whereCondition = " WHERE " & criteria
End If

' If there was a sort expression passed to the function, we build a ORDER BY for SQL
If Len(sortBy) > 0 Then
    sortExpression = " ORDER BY " & sortBy
End If
    
' building the complete SQL string
sql = "SELECT " & fieldName & " FROM " & tableOrQueryName & whereCondition & sortExpression & 
";"

' opening a recordset
Set rs = db.OpenRecordset(sql, dbOpenForwardOnly, dbReadOnly)
Do Until rs.EOF
    ' here we are looping through the records and, if the value is not NULL,
    ' concatenate the field value of each record with the delimiter
    If Not IsNull(rs.Fields(0).Value) Then
        retVal = retVal & Nz(rs.Fields(0).Value, "") & delimiter
    End If
    rs.MoveNext
Loop

' we cut away the last delimiter
retVal = Left(retVal, Len(retVal) - Len(delimiter))

' setting the return value of the function
QueryFieldAsSeparatedString = retVal

' cleaning up our objects
rs.Close
Set rs = Nothing
Set db = Nothing

End Function

This list is put into an array and used to dynamically create a batch number selection form, where each item in the list becomes a checkbox with the batch as a label. So an operator can choose which batch(es) have been worked on that day.

What my challenge/question is how would I go about sorting the batch string? The format is: 1, 1a, 2, 2a, 2b, ...

but comes in, in the order it is in the table. So it looks like 2a, 1, 2b, 1a, 1b, 2, ...

I'm going between the quicksort and figuring out a way to order/group the table before the string is even created, which may be in the second block of code; just not used properly.

The end goal would be to create another array consisting of the selected batches to continue data entry.

Any thoughts or questions are greatly appreciated!

Solution

  • elaborating on the comments. First use regular expressions to split batch into batch# and batchletters. https://medium.com/factory-mind/regex-tutorial-a-simple-cheatsheet-by-examples-649dc1c3f285 https://software-solutions-online.com/vba-regex-guide/

    Public Function RegexMatch(value As String, pattern As String) As String
'this code depends on adding a reference microsoft vbscript regular expressions 5.5 (in code window go tools-references)
Dim result As String
   If IsNull(value) Then Exit Function
   Dim regex As RegExp
 ' Initialise the Regex object '
   Set regex = New RegExp
        With regex
            .Global = True
            .IgnoreCase = False
            .MultiLine = True
            .pattern = pattern
        End With
 
  ' Test the value against the pattern '
  Set Matches = regex.Execute(value)
  If Matches.Count > 0 Then
  result = Matches.Item(0) 
  End If
  RegexMatch = result
End Function

    Batch#: RegexMatch([Batch],"(\d+)")
BatchLetters: RegexMatch([Batch],"(\D+)")

    Then create a query selecting everything and adding Batch# and BatchLetters. arrange variables in order you want to sort. I chose Shipment_Box, Product_Size, Batch#, BatchLetters

    query

    results

    Thats it. I saw no need to concatenate batch into a string as you can set the rowsource of a combobox or listbox directly to the query.