Add "and" to a string created by multi-select listbox

[Gina Maylone]

I'm sorry I thought it was apparent in my code


[CODE]For Each varSelected In ctl.ItemsSelected
strList = strList & ctl.Column(1, varSelected) & ", "
[CODE]

Truly sorry to have wasted your time.

[June7]

I am confused. If your code is concatenating values with comma then why is there a trailing 'and'? Where does 'and' come from? If anything, there should be a trailing comma.

[ssanfu]

The code in my example was not meant to say "Use an array". It was just to show how to replace the last comma with "And".
Changing a few variable names and dropping in the list box selections, here is the adapted code.

Be warned, I was not able test this, but it seems to compile.

    Dim ctl As Control
    Dim strList As String
    Dim varSelected As Variant
    Dim pos As Integer, i As Integer
    
    
    Me!ServiceText = sTemp

    i = 0  'counter

    Set ctl = Me!Needs

    If ctl.ItemsSelected.Count = 0 Then
        msgbox "You haven't selected anything"
    Else
        For Each varSelected In ctl.ItemsSelected
            strList = strList & ctl.Column(1, varSelected) & ", "
            i = i + 1
        Next varSelected

        'remove training space
        strList = Trim(strList)

        'remove last comma
        strList = Left(strList, Len(strList) - 1)

        If Len(strList) > 0 And i > 1 Then
            pos = InStrRev(strList, ", ")
            strList = Left(strList, pos - 1) & " and " & Right(strList, Len(strList) - pos)
        End If

    End If

    '    strList = Left(Me.ServiceText, InStrRev(Me.ServiceText, ",") - 2) & Replace(Me.ServiceText, ",", "and ", InStrRev(Me.ServiceText, ","))
    Me.ServiceText = strList

    'MsgBox "You selected the following items:" & vbCrLf & strList

[Gina Maylone]

I am confused. If your code is concatenating values with comma then why is there a trailing 'and'? Where does 'and' come from? If anything, there should be a trailing comma.

Yes, it concatenates like a,b,c and d - which is what I want it to do. But now I have the problem of "and" being at the end of the string, when no other chosen item follows.

Private Sub Needs_AfterUpdate()
    Me!ServiceText.Value = sTemp
    
    Dim ctl As Control
    Dim strList As String
    Dim varSelected As Variant
  
    Set ctl = Me!Needs
    If ctl.ItemsSelected.Count = 0 Then
    MsgBox "You haven't selected anything"
    Else
    For Each varSelected In ctl.ItemsSelected
      strList = strList & ctl.Column(1, varSelected) & ", "
    Next varSelected
'    ServiceText = Left$(strList, Len(strList) - 2)
    ServiceText = Left(strList, InStrRev(strList, ",") - 1) & Replace(strList, ",", " and ", InStrRev(strList, ","))
       
      End If
End Sub

Sorry for the confusion!

[June7]

That's because Replace() is replacing the trailing comma with ' and'.

So do another manipulation:

ServiceText = Left(ServiceText, Len(ServiceText) - 4)

[Micron]

I have a listbox that contains a list of services.

Well, I was under the impression that you were using a listbox, so I took a crack at it. I ended up with a final string variable called aryList, which when I substitute in June7's expression does not result in the answer I want. Maybe I did something wrong, but I have to admit, the expression is beautifully simple. I think the issue may be that if the last string portion is not one character long, it doesn't return enough. The posted code worked with a starting string of "400, 2R1, 3, 4, 5R1, 6, 8, 15, ". So, to allow any number of characters between commas at any position in the string, I came up with this:

Private Sub Command2_Click()
Dim ctl As Control
Dim strStart As String, strEnd As String
Dim aryList As String
Dim varSelected As Variant
  
Set ctl = Me!Needs
If ctl.ItemsSelected.Count = 0 Then
    MsgBox "You haven't selected anything"
    Exit Sub
End If

For Each varSelected In ctl.ItemsSelected
   aryList = aryList & ctl.Column(1, varSelected) & ", "
Next varSelected

'drop the last space and comma
aryList = Left(aryList, Len(aryList) - 2) 

'extract all from left before last remaining comma
strStart = Left(aryList, InStrRev(aryList, ",") - 1)

'get the last remaining digits
strEnd = Mid(aryList, InStrRev(aryList, ",") + 2)

'string both parts together with ' and ' in between
aryList = strStart & " and " & strEnd 
End Sub

You might want this to be a function instead of a sub. I tried to use your form/control names.
NOTE Re: Me!ServiceText.Value = sTemp that you have in your sub -
You don't seem to be using this, so I'm not sure if a caution is warranted. Looks like you are trying to set the value of a control to a variable rather than the other way around, which is normal. But I don't see where sTemp comes from, nor do I see you refreshing the form after setting the value of the control. Hope the code helps - took a long time because I origninally tried to build the string by nesting loops.

P.S. just noticed that it errs if the listcount = 1, so needs a bit of tweaking. Won't be doing that if you don't intend to use it.

[Gina Maylone]

This works beautifully! Except for the error that you mentioned when only one item is selected. Not sure what to do there. Thank you Micron!

[Micron]

You are welcome. Very glad it helped.
Copy what you have to a notepad document in case there's a problem, then replace everything between the Set statement and End Sub with this:

Select Case ctl.ItemsSelected.Count
   Case Is = 0
      MsgBox "You haven't selected anything"
   Case Is = 1
      aryList = ctl.Column(1, ctl.ItemsSelected(0))
   Case Is > 1
      For Each varSelected In ctl.ItemsSelected
          aryList = aryList & ctl.Column(1, varSelected) & ", "
      Next varSelected
      aryList = Left(aryList, Len(aryList) - 2)
      strStart = Left(aryList, InStrRev(aryList, ",") - 1)
      strEnd = Mid(aryList, InStrRev(aryList, ",") + 2)
      aryList = strStart & " and " & strEnd
End Select

All of this is provided with the understanding that you are happy with the correlation between the bound column of your listbox and the actual column the value is being pulled from, assuming there is more than one column. If not, there could be a mis-match between what is being constructed in the string versus what you expect. Verify the data is correct for each case (one selected item or more than one selected item).

[Gina Maylone]

SO SO close...I don't get an error when only one item is selected but it also doesn't set the value of that one item. I've added one line to the bottom of the code

Me.ServiceText = aryList

[Micron]

I didn't know what you wanted to do with it once the string was constructed. Glad you could figure it out.