Properly sort alphanumeric text field

[Trial4life]

Hi everyone,


I am trying to properly sort an alphanumeric field.

I often have fields containing something like "000-000-000". For example, if I sort by a simple ascending order, I get:

800-01-01
844-30-1
844-30-10
844-30-2
844-30-20

etc.

I've tried with the following expression in the advanced filter/sorting (also set by ascending):

Val(Replace([Fieldname];"-";""))

But what I get is:

844-30-1
844-30-2
800-01-01
844-30-10
844-30-20

As you can see, 800-01-01 gets in the way because the number itself (that results after removing the "-") is greater than 844-30-2, but lower than 844-30-10...

What I'm trying to achieve is to first order by the first set of numbers, then order by the second set of numbers, and then by the last one.
The second set of numbers can either be of 2 or 3 digits. The sorting should output something like this:

800-01-01
844-30-1
844-30-2
844-30-10
844-30-20

Thanks in advance for the help

[orange]

So, what sort order do you require from your sample data? Always better to show an example of before data and after/desired data.

You could separate the data based on the "-", then sort the left before middle before right end.
You might consider the Split function as an interim step.

[Trial4life]

Let's say the structure is "XXX-YYY-ZZZ", I require ascending order of XXX first, then ascending order of YYY, and finally ascending order of ZZZ.

[orange]

Try this sort of thing.
Where T4l is my table, and you chars to be sorted is in Myfield.

SELECT  * FROM T4L
order by
left(T4L.myField,3), mid(myfield,4,3),right(myfield,3)

The table T4L

ID	myField
1	844-30-1
2	844-30-20
3	844-30-2
4	844-30-10
5	800-01-01

Result of sort

ID	myField
5	800-01-01
1	844-30-1
3	844-30-2
4	844-30-10
2	844-30-20

[Welshgasman]

@orange,
That is going to fail when the second set is only 2 digits?
I would use a function to split the data and get back whichever set I needed?

Regardless the sets need to be split and sorted individually.

[Trial4life]

Thank you for the help. At first, I thought that coul work too. But the middle part of the field ("YYY") can be either of 2 or 3 digits.

I was thinking about a "Split" function, in order to get all the values before the first "-", then all the middle values, and then all the values after the last "-".

[Trial4life]

@orange,
That is going to fail when the second set is only 2 digits?
I would use a function to split the data and get back whichever set I needed?

Regardless the sets need to be split and sorted individually.

Exactly. Unfortunately, I've tried different "split" functions found googling around, but none of them seems to be working...

Which is the proper split function that I can use for this case?

[Welshgasman]

If I remember correctly, you cannot use Split() directly in a query.
You need to create a function that uses the Split()
Then pass back whichever index you require
You could even add the delimiter as a parameter?

Function fnSplit(pstrData As String, piIndex As Integer) As Integer
Dim strArray() As String


strArray = Split(pstrData, "-")
fnSplit = strArray(piIndex - 1)
End Function

Then call it with Expr1:fnSplit(FieldName,1),Expr2:fnSplit(FieldName ,2),Expr3:fnSplit(FieldName,3)
[/code]

Also change the return to be a number with Val() ?

[Trial4life]

This query alone seems to be working:

SELECT * 
FROM MyTable
ORDER BY
left(Fieldname,3), mid(Fieldname,4,InStr(4,Fieldname, "-")-InStr(Fieldname, "-")), mid(Fieldname,InStr(4,Fieldname, "-"),Len(Fieldname) - InStr(5,Fieldname, "-"))

But I cannot figure out how to implement it in the filter options of the form I'm trying to sort...

[Trial4life]

Okay, this is the working query:

SELECT * FROM 
ORDER BY
val(left(FieldTOSORT,3)), val(mid(FieldTOSORT,5,InStr(5,FieldTOSORT, "-")-InStr(FieldTOSORT, "-"))), val(mid(FieldTOSORT,InStr(5,FieldTOSORT, "-")+1,Len(FieldTOSORT) - InStr(5,FieldTOSORT, "-")))

The last thing to do is to make the subform (which loads the data using the following query: "SELECT [MyTable].[Field1], [MyTable].[Field2], [MyTable].[FieldTOSORT], [MyTable].[Field3] FROM MyTable;") auto reorder using the previous query... I've tryed to insert in the "order by" property the following expression:

"left(FieldTOSORT,3), mid(FieldTOSORT,4,InStr(4,FieldTOSORT, "-")-InStr(FieldTOSORT, "-")), mid(Fieldname,InStr(4,FieldTOSORT, "-"),Len(FieldTOSORT) - InStr(5,FieldTOSORT, "-"))"

but I get an error saying that the expression is too complex...

[Trial4life]

Okay it seems that I managed to make it work!
The expression is the one in the previous post:

val(left([FieldTOSORT],3)), val(mid([FieldTOSORT],5,InStr(5,[FieldTOSORT], "-")-InStr([FieldTOSORT], "-"))), val(mid([FieldTOSORT],InStr(5,[FieldTOSORT], "-")+1,Len([FieldTOSORT]) - InStr(5,[FieldTOSORT], "-")))

I added it in the "Order By" property of the subform, and set it to load the sorting when the subform is loaded. Even If set different sortings, whenever I close and reopen the form, the original sorting restores automatically.

The square brackets seem to be necessary to make it work.
Probably the expressione could be optimized, but it seems to be enough for my needs at this stage.

Thank you again for the support.

[ArviLaanemets]

In case any of 3 numeric parts of string never exceed 3 characters, the first and best option will be to change the field values to ones like "000-000-001", or "800-001-001", or "844-030-010", etc. Then you can simply order by field. You have some amount of work to create update queries to convert this field, and also all fields linked to this one in other tables, but so long as you never used leading zeroes there before, you can simply update all such fields without having any problems - and you will have never any ordering problems with this field in future. Of-course you have to ensure, that for all future entries the values for this field must be inserted in proper format. All other options (splitting this field to 3 calculated fields in query and converting those to numbers, or converting the field to calculated on with leading zeros in every part, and ordering the query on those calculated fields) are cumbersome, and make any query where you need such ordering to work much slower.

Or you simply replace this field in your table (and in all other fields linked with this one) with 3 numeric fields. In case you need the string like current one, you simply calculate it - like
Right("000" & CStr(NumericPart1),3) & "-" & Right("000" & CStr(NumericPart2),3) & "-" & Right("000" & CStr(NumericPart3),3)

[Trial4life]

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.

[Trial4life]

I reopen this post because I've found a flaw in the code that I was using to sort my data:

val(left([FieldTOSORT],3)), val(mid([FieldTOSORT],5,InStr(5,[FieldTOSORT],"-")-InStr([FieldTOSORT],"-"))), val(mid([FieldTOSORT],InStr(5,[FieldTOSORT],"-")+1,Len([FieldTOSORT])-InStr(5,[FieldTOSORT],"-")))

I've noticed that I also have some data (in the same field) in the form "XXX-YYY", or even "XXXX". In these cases, whenever I select a record in the main form that contains one of these records in the relative subform, I get the following error (obviously...):

"This expression is typed incorrectly or it is too complex to be evaluated"

because the final part of the name structure is missing. Is there a way to generalize the sorting, for example using a split function in order to sort n times, where n is egual to the number of "−" found in the name, minus 1?

[moke123]

Out of curiosity, do the 3 sections have any meaning individually? For instance in my area docket numbers like 2317-00782 consist of a 2 digit year, 2 digit court code, and a sequential number.
If so it may be easier to store them in separate fields.