Results 1 to 9 of 9
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    SORTING PROBLEM (A2K - SR1)

    I have a table that is copied from another application using the transfer database command. The field of interest in the copied table is the WBS field that imports as a text field. As an example, the WBS data imports in the following order as follows:
    C.12.1.1.1
    C.12.1.1.10
    C.12.1.1.2

    However, I need the WBS Order to be:
    C.12.1.1.1
    C.12.1.1.2
    C.12.1.1.10

    I can't get the order to work as required as the WBS field is a text field. To further complicate matters, many WBS elements end with an alpha character. ... and of course, there is not a standard structure in the WBS to make sorting simpler.

    Any ideas on how to re-sort the data in the table so I can get the data in the order required by the users??

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORTING PROBLEM (A2K - SR1)

    Gary,
    Put this function in a module and save the module with any name but Enlarge.
    The function split the string based on the dots and make each part 3 characters long, adding spaces before that part and put all the parts together in one string.
    You can add a field in a query as Enlarge(WSB) and sort on that.
    If you want, you can increase the width if necessary by changing the Space(3... to Space(x...
    <pre>Function Enlarge(txt As String) As String
    Dim ar() As String
    Dim x As Integer
    ar = Split(txt, ".")
    Enlarge = ""
    For x = 0 To UBound(ar)
    Enlarge = Enlarge & Space(3 - Len(ar(x))) & ar(x)
    Next x
    End Function
    </pre>

    Francois

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORTING PROBLEM (A2K - SR1)

    Francois - I have not run an experiment with your function (which I will do shortly), but - - - wouldn't Enlarge just change the size of the fields, but not change the sort order? I would think that the field would have to be (partly) numerical to obtain the order Gary wants.

    Interesting idea, sorting on an array

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORTING PROBLEM (A2K - SR1)

    The idea is not to change the field. It is to use two field in a query. One, the original WSB, visible, and the second, the expression Enlarge(WSB), sorted Ascending and invisible.
    As the digits will be on the same place in the string and all the string will have the same length, is doesn't matter if it is numeric or string.<pre>C.12.1.1.1 becomes C 12 1 1 1
    C.12.1.1.2 becomes C 12 1 1 2
    C.12.1.1.10 becomes C 12 1 1 10</pre>

    As a space have a smaller ascii value as the 1, the 2 will be sorted before the 10.
    Francois

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SORTING PROBLEM (A2K - SR1)

    Thanks Francois,

    I was thinking of something along the same lines as separating the WBS into segments, however, your function is much easier to use. I will let you know the results after I try it.

    <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SORTING PROBLEM (A2K - SR1)

    Francois,

    I created the function in a module, as shown below, however receive the following error message when running it.

    Run-time error '5'
    Invalid Procedure Call or Argument.

    The error is received on the following line:
    Enlarge = Enlarge & Space(3 - Len(ar(x))) & ar(x)

    Any ideas on how to resolve it.


    Function Enlarge(txt As String) As String
    Dim ar() As String
    Dim x As Integer
    ar = Split(txt, ".")
    Enlarge = ""
    For x = 0 To UBound(ar)
    Enlarge = Enlarge & Space(3 - Len(ar(x))) & ar(x)
    Next x
    End Function

    Thanks for all of your help.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORTING PROBLEM (A2K - SR1)

    That means that you have parts (between the dots) that are longer than 3 characters.
    Encrease the 3 in the line
    Enlarge = Enlarge & Space(3 - Len(ar(x))) & ar(x)
    to the maximum number of characters that can be between the dots.
    Francois

  8. #8
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORTING PROBLEM (A2K - SR1)

    ELEGANT! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SORTING PROBLEM (A2K - SR1)

    Thanks for the input. As soon as you said that there are more then 3 spaces between the dots I remembered that I had to truncate (or trim) the WBS to eliminate trailing spaces.

    The function works great. Thanks for the help.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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