• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

Sorting a text field with numbers

I am bringing a table into Access through ODBC. There is a text field with numbers, some numbers with a dash and possibly a letter  e.g., 6406-24, 6406-3, 6406-1T.  I would like to sort these as 6406-3, 6406-24 and 6406-1T.  Can any one come up with a way to do this?  Thanks.
0
Winnebago
Asked:
Winnebago
  • 6
  • 5
  • 4
  • +5
1 Solution
 
dovholukCommented:
if you are importing the data using a query, just order the field [desc]ending.  it should sort the text the way you want it. as 6406-3* comes after 6406-2* which comes after 6406-1*. by ordering descending, i would imagine it would sort correctly.

dovholuk
0
 
WinnebagoAuthor Commented:
I just ried this and it still does not work.  I got 6406-20, 6406-2, 6406-19.  I cannot have the 2 after the 20's, I would like it to be first.

Thanks for your help
0
 
kenspencerCommented:
Hi,

Instead of Descending try Ascending.

Ken
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
kenspencerCommented:
Hi again.

Perhaps some more info could help.  Your first comment indicates a descending order while your second comment indicates an ascending order.  Can you clarify?

Ken
0
 
WinnebagoAuthor Commented:
I originally wanted to sort ascending and have it set up this way.  The answer to my quesstion said to try descending thata's why I was showing it that way.  What I want to have it 6506-1, 6406-2, 6406-19. 6406-20.

Neither ascending or descending works because text fields are sorted differently than numeric fields.
0
 
dovholukCommented:
the original question didn't give the -2, vs -20 information. otherwise, i wouldn't have responded as such.

how about this then. using an access query, use the InStr() method  to find the leftmost numbers and then sort those numbers. (please check my syntax, as i've goto go...)

select field1, right(field1, len(field1) - instr(field1, "-")) as SortColumn
FROM YourTable
Order By SortColumn

just  a thought...

dovholuk
0
 
Arthur_WoodCommented:
Since you have a "complex" field, and you are trying to sort the data on parts of that single field, you will not be able to get the result you are requesting:( I assume the at what you showed:  6506-1, 6406-2, 6406-19,6406-20 was a typo, as you show 6506 BEFORE 6406).  YOu will need to break the COMPEX field into its two parts, and then sort on the two parts:

   Order By Part1 ASC, Part2 ASC

Which will then sort on Part1 and within  records where Part1 is the same, sort on Part 2.

There is no simple way to get the sort order that are trying to get, as a field with an EMBEDDED - will ALWAYS be seen by Access as a Text field, no matter what the pieces to the left and right of the - are.



 
0
 
LambertHeenanCommented:
You need tow simple function which will be used in a query. Here's the functions

Function SuffixValue(f As String) As Single
Dim sTemp As String
Dim nHyphen As Long
' Looks at the input string and strips off any hyphenated
' suffix - assumes the suffix is two "digits" or less
' is an all digit suffix is found then convert it to a
' number (between 0 and 99) and then divide it by 100 and
' returnn that value.
' If the suffix begins with a letter then get the numeric
' part only and return it as a whole number.
'
' The result is you can sort on the return value of this
' function and all numeric suffixes will come before mixed ones.

    ' get the suffix
    nHyphen = InStr(f, "-")
    If nHyphen > 0 Then
        sTemp = Nz(Mid(f, nHyphen + 1), "")
        If Not IsNumeric(Left(sTemp, 1)) Then
        ' we have an alpha prefix to the suffix
        ' assume it's only one character
            sTemp = Nz(Mid(sTemp, 2), "")
            SuffixValue = Eval(sTemp)
        Else
            SuffixValue = Eval(sTemp / 100)
        End If
    Else
        SuffixValue = 0#
    End If
End Function

Function PrefixValue(f As String) As String
Dim nHyphen As Long
' returns just the "numeric" prefix as a simple string
    nHyphen = InStr(f, "-")
    If nHyphen > 0 Then
        PrefixValue = Left(f, nHyphen - 1)
    Else
        PrefixValue = f
    End If
End Function


To do the sort you want simpy use these functions in your query, passing the field with the mixed numeric/alpha dat to them, for instance if your table is called "tblStringSortTest" and the field you want to sort is called "IDThing" then here is the SQL of a query that will do the desired sort...

SELECT tblStringSortTest.IDThing
FROM tblStringSortTest
ORDER BY PrefixValue([IdThing]), SuffixValue([IDThing]);


Note you sort first by the prefix and then by the suffix.
0
 
nico5038Commented:
I would use in a query:

 right("    " & left([YourCodeField],instr([YourCodeField],"-") - 1),4)  & right("    " & right([yourcodefield], Len([YourCodeField]) - instr([yourcodefield],"-")),4) AS SortField

This constructs one field with spaces as prefix, taking care that the alphanumeric sort will work.
Your problem is the "missing" space before the -2.
When the codes are differing like:
1 - 2
12-23T
6504-2
12345-3, etc.
Then take the longest as number of fields for the Right function. For the above sample you would need:

 right("    " & left([YourCodeField],instr([YourCodeField],"-") - 1),5)  & right("    " & right([yourcodefield], Len([YourCodeField]) - instr([yourcodefield],"-")),4) AS SortField

Clear ?

Nic;o)
0
 
kenspencerCommented:
Nico seems to have it.

Ken
0
 
LambertHeenanCommented:
nico:

"Your problem is the "missing" space before the -2. When the codes are differing like:"... etc.

That's why I use the function "SuffixValue" which takes the whole field and calculates a numerical value for the suffix, which is then used to sort by. If the suffix is all digits then the function returns a number between 0 and 1, and if the suffix includes alphas then the return value is > 1. Hence all numeric suffixes sort correctly and they are followed by all the sorted alpha suffixes, which is what winabago wanted.

BTW...
I too used to use "Right(string,Len(String) - Instr(String,"x")) to extract the right hand end of a string at a certain delimiter ("x"). Until I discovered that you can use Mid() to do the job more efficiently.

strX = Mid(String,Instr(String,"x")+1) will do the same job because the third parameter to Mid() is OPTIONAL. Leave it out and you get everything from the start position to the end of the string. No need to compute the length of the string!

Lambert
0
 
WinnebagoAuthor Commented:
Lambert:

Is SuffixValue the name of the function?  I could not find anything about it in 'help' and when I try to use it I get 'undefined function'.
0
 
nico5038Commented:
Good sorting point Lambert, I didn't notice the split of numeric/alphanumeric codes.
Normally I wouldn't expect a sort to split on this criterium as it's rather confusing to the user.

I can imagine that the code 6406-1T should follow e.g 6406-1 and that the T is a kind of suffix. This should imply that a trailing space is needed when 6406-1 is used too.

Tried your code but the Eval(sTemp/100) gives a syntax error...

Nic;o)
0
 
nico5038Commented:
Winnebago,

You need to copy/paste the code into a (new) module.
Then you're able to use the function name in your query.

BTW do you really want the 1T field after all numerics?

Nic;o)
0
 
LambertHeenanCommented:
nico:

"Tried your code but the Eval(sTemp/100) gives a syntax Error..."

Are you sure it's a syntax error and not a Type Mismatch error?

The code (reproduced below) assumes that the suffixes are of the form "-nn" or "-An", where 'n' is a digit and 'A' is a non-digit character, but I notice that you are talking about suffixes like "-1T". In that case the code will attempt to evaluate "T" which give a Type Mismatch.


       If Not IsNumeric(Left(sTemp, 1)) Then
       ' we have an alpha prefix to the suffix
       ' assume it's only one character
           sTemp = Nz(Mid(sTemp, 2), "")
           SuffixValue = Eval(sTemp)
       Else
           SuffixValue = Eval(sTemp / 100)
       End If
0
 
nico5038Commented:
Yep, I mistyped the error, it's a type mismatch.
I just used the sample value's as a test....

BTW if you just want the first numericdigit(s) a Val() function will work probably better as it will return a value 1 for 1T.
For the Alphanumeric separation we can use:
Function SuffixValue(f As String) As Single
Dim sTemp As String
Dim nHyphen As Long
' Looks at the input string and strips off any hyphenated
' suffix - assumes the suffix is two "digits" or less
' is an all digit suffix is found then convert it to a
' number (between 0 and 99)
' If the suffix begins with a number then get the numeric
' part, multiply with 1000 and add the ASCII value of the
' alphabetic character (Makes "1T" to appear before "1U")
'
' The result is you can sort on the return value of this
' function and all numeric suffixes will come before mixed ones.

   ' get the suffix
   nHyphen = InStr(f, "-")
   If nHyphen > 0 Then
       sTemp = Nz(Mid(f, nHyphen + 1), "")
       ' Here LEFT is changed into RIGHT.....
       If Not IsNumeric(Right(sTemp, 1)) Then
       ' we have an alpha containing suffix
       ' assume it's only one trailing character
           SuffixValue = Val(sTemp) * 1000 + Asc(Right(sTemp, 1))
       Else
           SuffixValue = Val(sTemp)
       End If
   Else
       SuffixValue = 0#
   End If
End Function


Giving:
Preff     Suff     YourCodeField
1234     2     1234-2
1234     3     1234-3
1234     20     1234-20
1234     1084     1234-1T
1235     2084     1235-2T

See why I prefer stringing in the query....

Nic;o)
0
 
WinnebagoAuthor Commented:
I don't want to make this more complicated, but I just looked at all the data and found the following numbers:  6617-1T(2), 990-0005, 90-0227-1, 7134-1T, 6598-1@2T.
0
 
LambertHeenanCommented:
Winnebago:

aargh!!!! All bets are off now! :-)

We'll have to think about this one some more.
0
 
WinnebagoAuthor Commented:
This is what I did.

IDLeft: Left([altreferralid],InStr(1,[altreferralid],"-")-1)

IDPartial: Right([altreferralid],(Len([altreferralid])-InStr(1,[altreferralid],"-")))

IDMiddle: Left([IDPartial],InStr(1,[IDPartial],"-")-1)

IDRight: Right([altreferralid],(Len([altreferralid])-InStr((InStr(1,[altreferralid],"-")+1),[altreferralid],"-")))

IDRightNum: Val([IDRight])

IDMiddleNum: Val([IDPartial])

IDLeftNum: Val([IDLeft])
0
 
nico5038Commented:
I would start asking the user what he expects when these fields are sorted.
Perhaps:
6617-  1T(2)
 990-   0005
  90- 0227-1
7134-     1T
6598-   1@2T
Is sufficient.....
And I would start with storing these fields in separate table fields too.

Nic;o)
0
 
joekendallCommented:
Winnebago:

Did you solve your sorting problem? If not, please post again.

Thanks!

Joe
0
 
amp072397Commented:
Winnebago:

I see that you logged in less than a week ago.

This is a free technical support site.

Experts who answer questions here receive no pay or other compensation except Expert points as a result answering questions and being awarded points.

As a result, and as a Moderator of this forum, my goal is to ensure that Experts and Askers alike no longer allow questions to get stale.

Please provide that feeback now. It's greatly appreciated!

If you need any help at all in cleaning up this or any other questions, don't hesitate to contact me via email. I will assist in any way that I can.

thanks
amp
community support moderator
amp@experts-exchange.com
0
 
WinnebagoAuthor Commented:
Sorry for not responding sooner,  I did something similar to this and it worked.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
  • 4
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now