Challenging select a portion of a text file as the sort order data

Question

Hi everyone,

I have a form named "frmFindProducts" that retrieve data based on specific information, the program is working well BUT I will like to sort it ascending based on a section of the field.

In other words, can I defined a part of a field to be sort by, see the example below, I would like the sort data to be everything after the last dash and sort on it. I could create another field and enter a sort number but it will be easier if someone know about this special sorting data.

Thanks for your help

Answer 1

In other words, can I defined a part of a field to be sort by, see the example below, I would like the sort data to be everything after the last dash and sort on it.

Hi Claude,

As the values in the four sections are exactly the same, you can sort on the whole field "Nom du produit" to have sorted on the value after the last dash.

But perhaps you want to sort when there are different values in the four sections. If the number of characters in the four sections is exactly the same, the you could use something like:

        SORT BY Mid"([Nom du produit],18)

With different numbers of characters in the four sections it will be a little bit more complicated, but solvable in my opinion. You coild use the InStrRev function to determine the position of the last dash, and sort from that position.

But if there can be dashes after the fourth dash ...

Imb.

Answer 2

This will require some basic string handling to extract the imperial measures.

I'll leave that, as you probably know how to do that, and an extended set of data will be needed to provide an exact method.

When done, you can use the code from my article here to convert and sort the imperial measures:

Convert and format imperial distance (feet and inches) with high precision

---

Gustav Brock

Answer 3

Thanks Imb, I did add a field named SortOrder in the table so the users can decide in which order they want the data to appear, it was becoming compllicated :)

Thanks to remind me of the Mid command

the 4 sections doesn't always have the same number of character, so it was becoming heavy

Claude

Answer 4

It's relatively easy to parse the string using the following function:

Function MySplit(strIn As String, iPos As Integer, strDel As String)

Const OUT_OF_RANGE = 9
Dim varSplit As Variant
   On Error GoTo MySplit_Error

    varSplit = Split(strIn, strDel)
    MySplit = varSplit(iPos - 1)

MySplit_Exit:
   Exit Function

MySplit_Error:
    Select Case Err.Number
        Case OUT_OF_RANGE
        MySplit = Null
    Case Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MySplit"
    End Select
    Resume MySplit_Exit
    
End Function

For example, calling the function in the immediate window gives the following results:

? MySplit("PP-STR.BB-SS-14g-1/4-3mm",5,"-") & ("-" + MySplit("PP-STR.BB-SS-14g-1/4-3mm",6,"-"))
1/4-3mm

? MySplit("PP-STR.BB-SS-14g-Sballs",5,"-") & ("-" + MySplit("PP-STR.BB-SS-14g-Sballs",6,"-"))
Sballs

However, sorting by the resulting substring will be alphabetical, so the fractional values will not sort correctly, e.g. 3/8 would sort before 5/16.  You can get round this by calling the Eval function, e.g. ? Eval(MySplit("PP-STR.BB-SS-14g-1/4-3mm",5,"-")) returns 0.25, so you could sort by this first and then by Val(MySplit("PP-STR.BB-SS-14g-1/4-3mm",6,"-")), which would return 3.  With this example both would sort correctly.

Eval(MySplit("PP-STR.BB-SS-14g-Sballs",5,"-")) on the other hand would raise a runtime error, so simply sorting on the expression will not work.  You'd need to wrap the expression in a function in which the error would be handled and the substring 'sBalls' returned.  However this would mean that the numeric value returned by the example in the previous paragraph would also need to be returned as a string expression.  To cause these to sort correctly therefore would require the values to be formatted with leading zeros so that they would sort numerically correctly as string expressions.

---

Ken Sheridan, Stafford, England

Answer 5

Thanks, I did put this link in my todo list, seems very interesting, I will study if I can apply that in my software somewhere.Claude

Answer 6

This query will sort your example data as requested:

SELECT
     Produit.[Nom du produit],
     Mid([Nom du produit],InStr([Nom du produit],"14-g-")+Len("14-g-")) AS Part,
     Abs(Right([Nom du produit],2)="mm") AS HasMeasure,
     ParseFeetInches(Mid([Nom du produit],InStr([Nom du produit],"14-g-")+Len("14-g-"))) AS Measure
FROM
     Produit
ORDER BY
     Abs(Right([Nom du produit],2)="mm") DESC,
     ParseFeetInches(Mid([Nom du produit],InStr([Nom du produit],"14-g-")+Len("14-g-"))),
     Mid([Nom du produit],InStr([Nom du produit],"14-g-")+Len("14-g-"));

---

Gustav Brock