Answered by:
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
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
- Marked as answer by Claude du Québec Monday, February 18, 2019 7:11 PM
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
- Marked as answer by Claude du Québec Monday, February 18, 2019 7:11 PM
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-"));
Gustav Brock
- Edited by Gustav BrockMVP Tuesday, February 19, 2019 9:29 AM
- Marked as answer by Claude du Québec Friday, March 8, 2019 1:17 PM
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
- Marked as answer by Claude du Québec Monday, February 18, 2019 7:11 PM
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
- Marked as answer by Claude du Québec Monday, February 18, 2019 7:11 PM
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-"));
Gustav Brock
- Edited by Gustav BrockMVP Tuesday, February 19, 2019 9:29 AM
- Marked as answer by Claude du Québec Friday, March 8, 2019 1:17 PM
Tuesday, February 19, 2019 9:28 AM