# Thread: SORTING PROBLEM (A2K - SR1)

1. ## 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>

2. ## 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>

3. ## 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. ## 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.

5. ## 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>

6. ## 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.

7. ## 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.

8. ## Re: SORTING PROBLEM (A2K - SR1)

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

9. ## 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>

#### Posting Permissions

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