Can a Query Sort a Comma Delimited String?

[David Ball]

Hi,

If I have a field of comma delimeted strings (like below) of data type Text, can I set up another field in a query to sort them alphabetically?
CN-MSTGN-GN-GN-010, CN-MSTGN-PI-PI-620, CN-MSTGN-GN-PI-640, CN-MSTGN-GN-GN-020
Note, that there are often duplicates in the string. This is OK, I don’t want to remove duplicates.
How would I do this?
Thanks very much
Dave

 

[MarkK]

Each of those should be in its own row in a related table, in which case sorting them would be trivial. To have them all in one field is a design flaw making them very difficult, as you can see, to work with.

To split them apart programmatically, check out the VBA.Split() function, which returns an array, which you can then enumerate, and add each value to a new row in a table.

 

[David Ball]

Hi Markk,

These strings are actually the Predecessors to Activities in Primavera P6 and this is how they are exported from P6. I don't want them separated. I just want to be able to compare the Predecessors to Activities between different versions of the same schedule. I do this by having a query that tells me whether or not the strings are equal.

This works fine, except in cases where I have the same Predecessors but in a different order (the order doesn't matter at all for my purposes). In these cases my query tells me the Predecessors are different when they are not. That is why I wanted to be able to sort them the same way.

Thanks

 

[MarkK]

You bet, cheers,

 

[sneuberg]

Hi Dave,

I thought I recognized this type of problem. I remember your post http://www.access-programmers.co.uk/forums/showthread.php?t=282019 where you were looking for the difference between two of these comma delimited strings. I and arnelgp (mainly arnelgp) spent a lot of time working on and solving that problem and we never heard a single word back from you. Not even that you didn't like the solution.

So I don't think forum member should help you when you don't show any gratitude whatsoever. Out of 116 posts you've only clicked the Thanks button three times.