Properly sort alphanumeric text field

[Trial4life]

The first to sections are associated to a category of objects, while the third is an incremental numbering.

[kd2017]

Write a function that processes and returns the codes properly formatted then sort by that. It's only needed for sorting and could be disregarded otherwise. Have it return something like "xxxxxyyyyyzzzzz", each of the three sections have extra leading zeros to cover odd case that has more digits , and if a group is missing plug in all zeros for that group.

[orange]

Trial4life,

It seems that your requirements have not been identified in full.

-The first to sections are associated to a category of objects, while the third is an incremental numbering.
-But the middle part of the field ("YYY") can be either of 2 or 3 digits.
-Okay it seems that I managed to make it work!
-This can't be a solution for my situation because these codes are associated with official document names, and sometimes they have leading zeros, sometimes not.

I suggest you take some real life examples - representing the various configurations.
List them, and then tell/show us what the proper sorting result should be.

People are offering well intended solutions to a "changing or incomplete requirement".

Good luck.

[Trial4life]

Right, I'll post the entire all of the possible configurations of my field I'm trying to sort, so maybe it will be more clear what I'm trying to do:

XXXX
XXXX-YY
XXXX-Y
XXX-YY
XXX-YY
XXX-YYY
XXX-Y-Z
XXX-Y-ZZ
XXX-Y-ZZ
XXX-YY-Z
XXX-YY-ZZ
XXX-YY-ZZZ
XXX-YYY-Z
XXX-YYY-ZZ
XXX-YYY-ZZZ

As you can see, the field is made of heterogeneous data. To generalize to result, the best thing would be to split the string each time a "-" is encountered, and if there is only one "-" (or even none), format it into a "xxxxxyyyyyzzzzz", as kd2017 suggested.

[kd2017]

Courtesy of chatgpt:

[kd2017]

I asked chatgpt to modifiy the function to handle missing groups of numbers:

Function FormatString(inputString As String) As String
    Dim groups() As String
    Dim formattedString As String
    Dim i As Integer
    
    ' Split the input string into groups
    groups = Split(inputString, "-")
    
    ' Format each group with zero left padding up to 4 digits
    For i = LBound(groups) To UBound(groups)
        groups(i) = Format$(Val(groups(i)), "0000")
    Next i
    
    ' Check if second group is missing and add "0000" at the end
    If UBound(groups) < 1 Then
        ReDim Preserve groups(1)
        groups(1) = "0000"
    End If
    
    ' Check if third group is missing and add "0000" at the end
    If UBound(groups) < 2 Then
        ReDim Preserve groups(2)
        groups(2) = "0000"
    End If
    
    ' Concatenate the groups with hyphens
    formattedString = Join(groups, "-")
    
    FormatString = formattedString
End Function

[Trial4life]

Many thanks, that could definitely work!

I'm just trying to figure out how to use this function in the advanced sorting... do I have to use another VBA sub that calls the FormatString function?

[kd2017]

Many thanks, that could definitely work!

I'm just trying to figure out how to use this function in the advanced sorting... do I have to use another VBA sub that calls the FormatString function?

You would just call the function from the query and sort by that column like so:


produces this:


*Note: So this is the sql engine making a call to a vba function one time for every record returned, calling vba from queries like this can be inefficient. Generally speaking I try not to do this very often, if I do it's on a filtered/limited number of rows. If there are sql native ways to accomplish something, I usually try to do that first.

[Trial4life]

I've been trying the exact same thing for the last hour, but I always get the following error:

"Undefined function "FormatString" in expression"

What am I doing wrong? It seems like the query is not recognizing the function (I've placed it into a dedicated module).

Regarding your note, the sorting will always run on a limited amount of records, so probably it won't be an issue. Anyways, is it possible to achieve the same result only using SQL?

[Trial4life]

Okay, it turned out that Access didn't like the function name "FormatString" (I checked and it was the only function with that name in the entire database... so I have no clue why); however, renaming that function to "FormatStringV2" everything worked fine.

Thank you again for your help and your patience!

[kd2017]

You're welcome. And good work figuring out the naming issue. The name FormatString is awfully generic, if I had written it I probably would have named it something a bit more specific. Also, I was going to suggest maybe specifying Public Function instead of just Function.

[Trial4life]

Strange thing: I get the same error if I rename the .ACCDB file and reopen it. What could be causing this issue?

[CarlettoFed]

Maybe if you attach an example file it's easier to understand what's going on and help you.

[Trial4life]

I've figured it out: every time I rename the .ACCDB file (or if I move it somewhere else), the first time I reopen it, it blocks the macros and asks me if the file is trusted. Before I accept and continue, the function "Format_String" is still not defined (since the macros are dsabled), and I get the error messages.

https://imgur.com/ZogKPVN

Is there a way to bypass this procedure (or at list prevent the "Format_String" error window from showing up)?

[isladogs]

@kd2017
I've just skimmed this thread including post #26
For info, writing Public Function is functionally equivalent to writing Function. All functions in a standard/class module are public unless specifically marked as Private

@Trial4life
Place the file in a trusted location