Sort

[Korp26]

I have pulled numbers like these from a table and it sorts them 1,10,11... I want them 1,2,3....and so on.
Good example
250-HP-1401 CCT1: IN-250-TR-4030-01; IN-250-TR-4031-01
250-HP-1401 CCT2: IN-250-PW-4020-04

Bad example
250-HP-1401 CCT1: IN-250-TR-4030-01; IN-250-TR-4031-01
250-HP-1401 CCT10: SPARE

I have extracted the singular number from the rest of the sting hoping i could just low to high sorted them and all would fall in place. but im coming up with a invalid procedure call?

 

[CJ_London]

since that is where the problem is, what code are you using to extract?

 

[arnelgp]

...Order By Val(Replace$(cctField, "CCT", ""))

 

[arnelgp]

you can also create a function, if each row in post#1 is on single field:

Public Function fncNumFromCCT(ByVal p As Variant) As Long
    Dim vr
    p = p & vbNullString
    If Len(p) < 1 Then Exit Function
    With CreateObject("vbscript.regexp")
        .Global = True
        .ignorecase = True
        .pattern = "CCT[0-9]{1,4}"
        vr = .Execute(p)(0)
        fncNumFromCCT = CLng((Replace$(vr, "CCT", "")))
    End With
End Function

on your Query:

...Order By fncNumFromCCT([cctField])

 

[Korp26]

since that is where the problem is, what code are you using to extract?

CCT: Mid([Line-1],InStrRev([Line-1]," ")+4)

 

[arnelgp]

?undefined function Replace$()?

 

[The_Doc_Man]

Here is the problem. You have data in text format for which the sorting rules start from the left and expand to the right. You want numeric sorting for which the rules start from the right and expand to the left. As long as that field is like it is, with variable-length text strings, you cannot do the sort like you want. HOWEVER... like in other programming languages, when faced with an impossible situation, change the situation.

IF you have a way to do it, figure out the maximum width of the number that follows your CCT portion of the string. THEN from there, figure out the length of the string between that "T" in "CCT" and the colon. Then break apart the string to insert leading zero characters so that the strings look like

250-HP-1401 CCT01: IN-250-TR-4030-01; IN-250-TR-4031-01
250-HP-1401 CCT10: SPARE

IF all of the strings are the same size up to the colon, then they will sort properly. Note that if you can't do that, then you have a situation that is incompatible with your sort goals. There are still other ways to do it but they require other "compromises" in your setup.

 

[Korp26]

Here is the problem. You have data in text format for which the sorting rules start from the left and expand to the right. You want numeric sorting for which the rules start from the right and expand to the left. As long as that field is like it is, with variable-length text strings, you cannot do the sort like you want. HOWEVER... like in other programming languages, when faced with an impossible situation, change the situation.

IF you have a way to do it, figure out the maximum width of the number that follows your CCT portion of the string. THEN from there, figure out the length of the string between that "T" in "CCT" and the colon. Then break apart the string to insert leading zero characters so that the strings look like

250-HP-1401 CCT01: IN-250-TR-4030-01; IN-250-TR-4031-01
250-HP-1401 CCT10: SPARE

IF all of the strings are the same size up to the colon, then they will sort properly. Note that if you can't do that, then you have a situation that is incompatible with your sort goals. There are still other ways to do it but they require other "compromises" in your setup.

I’m going to work on this, thanks for the explanation. Helpful..

 

[CJ_London]

CCT: Mid([Line-1],InStrRev([Line-1]," ")+4

instrrev won't work because in this line for example

250-HP-1401 CCT1: IN-250-TR-4030-01; IN-250-TR-4031-01

there is a space after the last semi colon

and the mid function without a number of characters to include will return everything after, i.e. 250-TR-4031-01

think what you need is the first space and the val function to return a number. try

CCT: val(Mid([Line-1],InStr([Line-1]," ")+4)

 

[Korp26]

CCT: Mid([Line-1],InStrRev([Line-1]," ")+4

instrrev won't work because in this line for example

250-HP-1401 CCT1: IN-250-TR-4030-01; IN-250-TR-4031-01

there is a space after the last semi colon

and the mid function without a number of characters to include will return everything after, i.e. 250-TR-4031-01

think what you need is the first space and the val function to return a number. try

CCT: val(Mid([Line-1],InStr([Line-1]," ")+4)

Okay that’s easier than my way. Isolating it from the original sting is better. I had done it differently. Seeing yours makes me also see less chance for mistakes, always pulling info from one source.
Good stuff.