Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Sep 2003
    Location
    USA
    Posts
    24

    Angry Unanswered: numeric sort on an alphanumeric field

    We have an alphanumeric field whose text/width is in no standard order and our query presents it sorted for user selection e.g., we get:

    C203L
    C2L 1
    D1L
    D201L
    D2L
    DC1L
    DC2L

    Which should be sorted such as:

    C2L 1
    C203L
    D1L
    D2L
    D201L
    DC1L
    DC2L

    (This is a list fragment of a very long list)

    I am at my wits end as to how can I accomplish this in my query????

    Any help at all will be greatly appreciated. Thanks!!!!!

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Is it always

    Letter(s) Number(s) Letter(s)

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Rather, is it always one of the following:


    Letter(s)

    Letter(s) Number(s)

    Letter(s) Number(s) Letter(s)

    Letter(s) Number(s) Letter(s) Number(s)


    etc.

  4. #4
    Join Date
    Sep 2003
    Location
    USA
    Posts
    24
    Pretty much

    Letter(s) Number(s) ending in L

    the
    C2L 1
    C2L 2 would be important up to the L

  5. #5
    Join Date
    Sep 2003
    Location
    USA
    Posts
    24
    On second thought, they will probably want the

    C2L 1 and
    C2L 2 to align this way

    but the others are all

    letter(s) number(s) ending in L

    thanks!

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    So, you can parse this identifier into four segments?

    Code:
    Segment 1   Segment2   Segment3   Segment4
    C                2                L                1
    C                203             L
    D                1                L
    D                2                L
    D                201             L
    DC              1                L
    DC              2                L
    Then do a primary sort on Segment1, a secondary sort on segment2, etc., etc. ?

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The only way you are going to change the sort order is to pull out the part you want to sort by. You will want two fields in the query, one is the actual data and the second is the numeric data from the acutal data so that you results will look like:

    C2L 1--------| 2
    C203L-------| 203
    D1L ---------| 1
    D2L ---------| 2
    D201L ------|201
    DC1L -------|1
    DC2L -------|2

    The calculated field (2,203,1,2) will be used strictly to sort the data. You don't even need to show it.

    I would suggest writing a function that takes the original data and reads one character at a time until you find a number. Then copy all the characters to a variable until you get to an L.

  8. #8
    Join Date
    Dec 2003
    Posts
    268

    Nevermind

    I am just posting so I can see how you guys resolve this one. This one sounds pretty interesting. I will toss it around and see if I can come up with anything too.

  9. #9
    Join Date
    Dec 2003
    Posts
    268

    What I came up with

    This one did prove to be alittle difficult. I probably spent more time on this than I should have, but it seems to work with the provided records. Test it out and see how it works with a bigger recordset.

    Background:

    This was done useing DAO, not ADO so you may need to tweek your code a little to make it work.

    1. Create a temp table with 4 attributes; segment1, segment2, segment3, segment4
    datatypes of text, numeric, text, numeric respectively

    2. Create a new module

    3. Paste this code into the module

    '********************************************
    Option Compare Database

    Sub buildTable()
    Dim db As Database
    Dim rs As Recordset
    Dim str1, str2, str3, str4 As String
    Dim temp As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM table1")
    rs.MoveFirst
    While Not rs.EOF
    'clear values
    segment = 0
    str1 = ""
    str2 = ""
    str3 = ""
    str4 = ""
    'store value for next record
    temp = rs.Fields(0)
    str1 = getSeg(temp, True)
    temp = Mid(rs.Fields(0), Len(str1) + 1)
    str2 = getSeg(temp, False)
    temp = Mid(rs.Fields(0), Len(str2 & str1) + 1)
    str3 = getSeg(temp, True)
    temp = Mid(rs.Fields(0), Len(str3 & str2 & str1) + 1)
    str4 = getSeg(temp, False)
    db.Execute ("INSERT INTO Table2 VALUES(" & addquotes(Nz(str1, "")) & "," & IIf(Len(str2) = 0, 0, str2) & ", " & addquotes(Nz(str3, "")) & ", " & IIf(Len(str4) = 0, 0, str4) & ")")
    rs.MoveNext
    Wend
    End Sub
    Function getSeg(str As String, Alpha As Boolean) As String
    Dim x As Integer
    Dim temp As String
    Dim tempString As String
    If Alpha Then
    For x = 1 To Len(str)
    temp = Mid(str, x, 1)
    If Not (IsNumeric(temp)) Then
    tempString = tempString & temp
    Else
    getSeg = tempString
    Exit Function
    End If
    Next x
    Else
    For x = 1 To Len(str)
    temp = Mid(str, x, 1)
    If IsNumeric(temp) Then
    tempString = tempString & temp
    Else
    getSeg = tempString
    Exit Function
    End If
    Next x
    End If
    'accounts for only 1 char length string
    getSeg = tempString
    End Function


    Function addquotes(str As String) As String
    addquotes = Chr(34) & str & Chr(34)
    End Function


    '**********************************
    'end of code


    Disclaimer: This probably isn't the most graceful code in the world, but it works There are some redundancies, which I am sure can be hacked out to make it work a little faster. But anyhoo, it works.

    Just run the makeTable sub and it should parse out the four segments paste them to a table, then you can sort by the four columns to get the information in an order you want, in this instance, select all four columns and sort in ascending order. Then to join this on another string you can create a trunkated column :
    Item: segment1 & segment2 & segment 3 & segment 4

    The reason I did it this way was because to try and do the parsing in a query without any type of indicator as to when you should be switching to the next sort option (segment) and the fact the each segment is a dynamic length(1,2, 3, 0) I thought it best to create anew table.

    You may also notice an additional function called addquotes. I use this to add "" around strings when passing to a String that is to be used in a SQL statement. You an disregard this but need to make sure that your syntax on the insert is correct.

    I hope this works for you. If you come up with a different method or algorithm let me know I would be interested.

  10. #10
    Join Date
    Sep 2003
    Location
    USA
    Posts
    24
    Thank you, I will play with this; I hope to accomplish this without having to create temp tables as tmp table creation is frowned on in the app this is a part of.

    The data is coming from a table and being presented to the user in a list box through a query so that they may select it to drive something else.

  11. #11
    Join Date
    Dec 2003
    Posts
    268

    Do it only once

    Perhaps you could do it just once and append records to the table whenever you get new items to add. I was thinking about this a little more and came up with a different approach.

    doing this type of processing in a query is going to be impossible for two reasons.
    1. To dynamically search through a string to pull information out and sort in a column is not doable. Youcan do mid, right, left etc. but to step through each character in a string and verify that the following character is not of the same datatype yo wont be able to do.

    2. There is no type of indicator to when the change of the segments were to occur i.e. - or , or somethign like that you do in a query, but seeing as the place for these indicators is it would prove difficult to try and do. Of course you could do it with a rather complicated nested query.

    Hope this helps.

    One other thing to consider, you coudl append to the new table everytime a new record is created. It woudl not be too difficult to copy the create table sub and turn that into something that can recieve a string, then insert the new record. This will help prevent bloat of the DB, which is what I am assuming the 'people' who frown upon the temp table creation.

    following is the revised code.

    '********************************************
    Option Compare Database

    Sub buildTable()
    Dim db As Database
    Dim rs As Recordset
    Dim str1, str3 As String
    Dim str2, str4 As Integer
    Dim temp As String
    Dim i As Integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM table1")
    rs.MoveFirst
    While Not rs.EOF
    'clear values
    segment = 0
    str1 = ""
    str2 = 0
    str3 = ""
    str4 = 0
    'store value for next record
    temp = rs.Fields(0)
    str1 = getNextString(temp)
    temp = Mid(rs.Fields(0), Len(str1) + 1)
    str2 = getNextIntString(temp)
    temp = Mid(rs.Fields(0), Len(str2 & str1) + 1)
    str3 = getNextString(temp)
    temp = Mid(rs.Fields(0), Len(str3 & str2 & str1) + 1)
    str4 = getNextIntString(temp)
    db.Execute ("INSERT INTO Table2 VALUES(" & addquotes(Nz(str1, "")) & "," & str2 & ", " & addquotes(Nz(str3, "")) & ", " & str4 & ")")
    rs.MoveNext
    Wend
    End Sub
    Function getNextString(str As String) As String
    Dim x As Integer
    Dim temp As String
    Dim tempString As String
    For x = 1 To Len(str)
    temp = Mid(str, x, 1)
    If Not (IsNumeric(temp)) Then
    tempString = tempString & temp
    Else
    getNextString = tempString
    Exit Function
    End If
    Next x
    'accounts for only 1 char length string
    getNextString = tempString
    End Function

    Function getNextIntString(str As String) As Integer
    Dim x As Integer
    Dim temp As String
    Dim tempString As String

    For x = 1 To Len(str)
    temp = Mid(str, x, 1)
    If IsNumeric(temp) Then
    tempString = tempString & temp
    Else
    getNextIntString = CInt(tempString)
    Exit Function
    End If
    Next x
    'accounts for only 1 char length string
    getNextIntString = CInt(tempString)
    End Function
    Last edited by mjweyland; 04-01-04 at 13:02.

  12. #12
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    "Youcan do mid, right, left etc. but to step through each character in a string and verify that the following character is not of the same datatype yo wont be able to do."

    Why not ?

    strValue="DC123L2"
    LastCharacterIsNumber=0

    For i=2 to len(strValue)

    If isnumeric(mid$(strValue,i,1)<>LastCharacterIsNumbe r

    LastCharacterIsNumber=abs(LastCharacterIsNumber)-1
    ... a change has occured, record the postion and do whatever else you want to do

    EndIf

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It can be done. When I first read your post I misundertood it. I thought you only need the numeric portion. But you also need the alpha portion at the beginning.

    I don't have time to write the functions for you, but all you nned to do is write a function that strips off the alpha portion of the data and returns it. Ex. DC123L2 returns DC or C123L2 returns C. Use is numeric to determine if you gotten to the first numeric.

    Then write another function that will return the numeric portion. Skip over any non numerics and when you get to the numerics put that in a string until you get to the L.

    The final step is to put a calculated field in your query. In the design grid it would look like:

    NumericPortion:GetNumericPortion([FieldValue])

    AlphaPortion:GetAlphaPortion([FieldValue])

    FieldValue

    The data in the query would look like:

    DC123L2-------DC-------123

    Then just sort the DC and the 123 however you want.


    This can also be expanded to each segment if you need to sort with anything after the L.

  14. #14
    Join Date
    Dec 2003
    Posts
    268

    Account for more than one segment

    Originally posted by PracticalProgram
    "Youcan do mid, right, left etc. but to step through each character in a string and verify that the following character is not of the same datatype yo wont be able to do."

    Why not ?

    strValue="DC123L2"
    LastCharacterIsNumber=0

    For i=2 to len(strValue)

    If isnumeric(mid$(strValue,i,1)<>LastCharacterIsNumbe r

    LastCharacterIsNumber=abs(LastCharacterIsNumber)-1
    ... a change has occured, record the postion and do whatever else you want to do

    EndIf
    That will work if you only need stuff from one end adn the starting point is definitevely 2, how are you going to handle that dynamically for each segment in the string?

    i.e.
    D1L2
    DC1L2
    DC101L201
    C103l2

    for each of these strings the starting points for segment 2, 3 or 4 are all different from eachother. Yet this still needs to be sorted.

    If there is a different methodolgy I would love to hear about it.

  15. #15
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    To the originator of this posting . . .

    as you can see, from the responses, you can accomplish just about anything you need.

    There is a tradeoff, however. Going through every character of this field in every record is okay if you have 100 records, or a 1,000 records, or even 10,000 records. But if you have to do this for 50,000 records, then this process is impractical.

    The proper thing to do, and it should have been done in the database design phase of this project, was to separate the segments into different fields. Then the sort could be done in a query, rather than in code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •