Sort concatenated txtbox in unbound field 4

[jw5107]

I have a unbound txtbox on a form that shows a concatenated value like:
TYS, DFW, ONT, ABY, GEG

Is there a way (possibly on DblClick??) to allow the user to sort the concatenated value A-Z if desired to do so?

I am not sure as to what function to use???

Thanks,

JW

 

[SteveR77]

JW,

I can give you the steps but it will take me some time to write all the code needed to do this.

1 - Parse the value of the textbox string and put it into an array. (You will need to use Instr to do this)

2 - Sort the array element values using a sort function like the one below -


Public Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 0 To UBound(TheArray) - 1
If TheArray(X) > TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function


3 - Step through the sorted array elements and concatenate the element values back into a single string.


4 - Assign the new string built from the array elements back to the textbox value.


The problem is while all this sound simple it is really rather complex.


I'll see what I can come up with and if someone should bet me to the answer, thanks in advance.


Steve

 

[RoyVidar]

Hi!

Couldn't resist the temptation SteveR77, but it's a slightly different variant, using the split function:

[tt] Dim myArr() As String
Dim i As Long
Dim j As Long
Dim s As String
Dim sSep as String

sSep = ","
s = Forms!YourForm!YourTextBox.Value
myArr = Split(s, sSep)
For i = 0 To UBound(myArr)
myArr(i) = Trim$(myArr(i))
Next i
For i = 0 To UBound(myArr) - 1
For j = i + 1 To UBound(myArr)
If myArr(i) > myArr(j) Then
s = myArr(i)
myArr(i) = myArr(j)
myArr(j) = s
End If
Next j
Next i
s = vbNullString
For i = 0 To UBound(myArr)
s = s & myArr(i) & sSep & " "
Next i
s = Left$(s, Len(s) - 2)
Forms!YourForm!YourTextBox.Value = s[/tt]

Roy-Vidar

 

[SteveR77]

I created a button and dropped the code in to test it out and everything worked perfectly.

Thanks Roy-Vidar!

I had the steps but I know I would not have remembered the split command until after it was all done.

I will add this one into my library of code. I know it will be useful so a star is coming your way.

 

[jw5107]

Royvidar,

I have to give ya some shout outs for this code!!

Thank you very much for chimemin' in and breakin' it down!!!

One star coming right up!!

JW

 

[PeteJohnston]

Nice one Roy-Vidar

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)