Text/Number/special character sorting problem 2

[gwoman]

I have a field with Generator Names that contain both text, numeric and hyphens and I am having an issue with the sort order .... any suggestions would be greatly appreciated! I know this is probably an easy fix ... apparently my brain isn't cooperating this morning!

Example 1 of current sort order:

CG1
CG2
CG3
Gen103
Gen104
Gen105
Gen106
Gen23
Gen24
Gen29
Gen89
Gen90

Example 2 of current sort order:

Gen11-1A
Gen11-1B
Gen11-1C
Gen1-1A
Gen1-1B
Gen1-1C
Gen12-2A
Gen12-2B
Gen12-2C
Gen2-1D
Gen2-1E
Gen2-1F

Example 1 of sort order I want:
CG1
CG2
CG3
Gen23
Gen24
Gen29
Gen89
Gen90
Gen103
Gen104
Gen105
Gen106


Example 2 of sort order Iwant:

Gen1-1A
Gen1-1B
Gen1-1C
Gen11-1A
Gen11-1B
Gen11-1C
Gen12-2A
Gen12-2B
Gen12-2C
Gen2-1D
Gen2-1E
Gen2-1F


Thanks!

gwoman

 

[SiJP]

When access is sorting variant fields, it takes each literal character and sort's it accordingly by string value.

In your preferred instance of..
G-e-n-9-0
G-e-n-1-0-3
..logically 1 comes before 9, so access will not sort it this way. Access does not know that 103 is to be treated as a whole number.

Theoretically, you would need to manipulate the field data e.g. change to Gen090 or even remove the Gen part from it.

This second bit could be done by creating a hidden field on your query, and creating a mask so only the numerical figures are returned, formatted to (for example) three digits. Pad out any missing digits with 0's and you should have a viable solution.

------------------------
Hit any User to continue

 

[gwoman]

Thanks ... my thinking as well ... however I have a couple of questions ...
How would I extract the numeric data when it contains a hyphen? What will the results be say if there is no numeric data in the name? And if I extract the numeric data how will it sort the text part?
Thanks ... I hope my questions make sense

 

[SiJP]

I understand your question, but it's a bit difficult to apply all your rules without a little code. So here goes.

Create the following two functions in a new module.

  Public Function RetNumber(sFldValue As String) As Long
    Dim str As String
    Dim i As Integer
    str = ""
    For i = 1 To Len(sFldValue)
        If IsNumeric(Mid(sFldValue, i, 1)) Then
        str = str & Mid(sFldValue, i, 1)
        End If
    Next
    RetNumber = Nz(CLng(Trim(str)), 0)
  End Function

  Public Function RetAlpha(sFldValue As String) As String
    Dim str As String
    Dim i As Integer
    str = ""
    For i = 1 To Len(sFldValue)
        If Not IsNumeric(Mid(sFldValue, i, 1)) Then
            str = str & Mid(sFldValue, i, 1)
        Else
            GoTo exit_f 'Exits at the first sign of numbers
        End If
    Next

exit_f:
    RetAlpha = Nz(Trim(str), "")
  End Function

Now create the following query, replacing your field and table names:

SELECT tblIDs.ID
FROM tblIDs
ORDER BY RetAlpha([ID]), RetNumber([ID]), IIf(Not IsNumeric(Right([id],1)),Right([id],1),"");

------------------------
Hit any User to continue

 

[gwoman]

This is great .... many thanks!

I am getting a "type mismatch" runtime error on the following line of code in the RetNumber Function:

RetNumber = Nz(CLng(Trim(str)), 0)

Here is my query:

SELECT dbo_LST_STGN_INJ_SRCE_PPTY.FAC_NME
FROM dbo_LST_STGN_INJ_SRCE_PPTY
ORDER BY RetAlpha([dbo_LST_STGN_INJ_SRCE_PPTY.FAC_NME]), RetNumber([dbo_LST_STGN_INJ_SRCE_PPTY.FAC_NME]), IIf(Not IsNumeric(Right([dbo_LST_STGN_INJ_SRCE_PPTY.FAC_NME],1)),Right([dbo_LST_STGN_INJ_SRCE_PPTY.FAC_NME],1),"");

gwoman

 

[PHV]

You may try this:
RetNumber = Val(str)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gwoman]

Thanks ... it works! You guys are awesome!

It's close and you guys have been a big help ... I really appreciate it!

Note the order below ... Gen25 and Gen26??

FAC_ID FAC_NME PAET_FAC_NME
121850 Gen2-3G Belridge
121851 Gen2-3H Belridge
121852 Gen2-3I Belridge
121853 Gen2-3J Belridge
121901 Gen25 Belridge <<<
121855 Gen2-5A Belridge
121857 Gen2-5B Belridge
121858 Gen2-5C Belridge
121859 Gen2-5D Belridge
121860 Gen2-5E Belridge
121861 Gen2-5F Belridge
121862 Gen2-5G Belridge
121863 Gen2-5H Belridge
121864 Gen2-5J Belridge
121807 Gen26 Belridge <<<
121865 Gen3-2 Belridge
121866 Gen3-2A Belridge
121867 Gen3-2B Belridge
121868 Gen3-2C Belridge

Thanks!

gwoman

 

[gwoman]

The more I work with it .. I am finding that it's not handling the hyphen properly. My attempts in handling it have been to no avail ... any ideas on how to deal with the hyphen?

Thanks ...
gwoman