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

  • 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

    Monday, February 18, 2019 4:47 PM

Answers

  • 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

    Monday, February 18, 2019 6:55 PM
  • 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

    Monday, February 18, 2019 7:09 PM
  • 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-"));


    Example


    Gustav Brock


    Tuesday, February 19, 2019 9:28 AM

All replies

  • 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.

    Monday, February 18, 2019 6:45 PM
  • 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

    Monday, February 18, 2019 6:55 PM
  • 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

    Monday, February 18, 2019 7:09 PM
  • 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

    Monday, February 18, 2019 7:09 PM
  • 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

    Monday, February 18, 2019 7:11 PM
  • 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-"));


    Example


    Gustav Brock


    Tuesday, February 19, 2019 9:28 AM