Solved

Sorting a text field with numbers

Posted on 2001-07-10
23
490 Views
Last Modified: 2012-08-13
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
Comment
Question by:Winnebago
  • 6
  • 5
  • 4
  • +5
23 Comments
 
LVL 8

Expert Comment

by:dovholuk
ID: 6269562
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
 

Author Comment

by:Winnebago
ID: 6269597
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
 
LVL 3

Expert Comment

by:kenspencer
ID: 6269608
Hi,

Instead of Descending try Ascending.

Ken
0
 
LVL 3

Expert Comment

by:kenspencer
ID: 6269625
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
 

Author Comment

by:Winnebago
ID: 6269666
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
 
LVL 8

Expert Comment

by:dovholuk
ID: 6269687
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6269900
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
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 6269958
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 100 total points
ID: 6270553
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
 
LVL 3

Expert Comment

by:kenspencer
ID: 6273281
Nico seems to have it.

Ken
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 6273502
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Winnebago
ID: 6273629
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
 
LVL 54

Expert Comment

by:nico5038
ID: 6273759
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
 
LVL 54

Expert Comment

by:nico5038
ID: 6273904
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
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 6274333
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
 
LVL 54

Expert Comment

by:nico5038
ID: 6274803
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
 

Author Comment

by:Winnebago
ID: 6276851
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
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 6277034
Winnebago:

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

We'll have to think about this one some more.
0
 

Author Comment

by:Winnebago
ID: 6281587
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
 
LVL 54

Expert Comment

by:nico5038
ID: 6281776
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
 
LVL 11

Expert Comment

by:joekendall
ID: 6510346
Winnebago:

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

Thanks!

Joe
0
 

Expert Comment

by:amp072397
ID: 6744747
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
 

Author Comment

by:Winnebago
ID: 6807928
Sorry for not responding sooner,  I did something similar to this and it worked.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dateadd 3 22
DCount using "OR" 4 19
Multiple queries for a form 14 22
Join vs where 2 0
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now