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

[Gina Maylone]

Good morning all!

I have a listbox that contains a list of services. Some people request more than one service at a time - so I have it set to accept multiple selections (please, no lectures, it works well). From this box I have another box where a string of choices is written out, separated by commas. I would like to know if there is ANY way that I could have the word "and" instead of a comma preceding the last selection. Here's my code:

Me.ServiceText = Me.Needs.Column(1) & ","
    Dim oItem As Variant
    Dim sTemp As Variant
    
    Dim iCount As Integer
    
    iCount = 0
            
    If Me!Needs.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!Needs.ItemsSelected
            If iCount = 0 Then
                sTemp = sTemp & Me!Needs.ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp = sTemp & "," & Me!Needs.ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        'MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If
    
    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
    strList = Left$(strList, Len(strList) - 2)
    Me.ServiceText = strList
    
    'MsgBox "You selected the following items:" & vbCrLf & strList
  End If

As always, thank you in advance for any suggestions!! Have an awesome day!

[CJ_London]

just replace

sTemp = sTemp & "," & Me!Needs.ItemData(oItem)


with

sTemp = sTemp & " and " & Me!Needs.ItemData(oItem)

but depending on your requirements comma's are OK for example

Where myfield in (1,4,5,6)

[Gina Maylone]

Thanks for your response Ajax - however - I only want the LAST selected item to be preceded by "and". the commas are good for the rest of it. I use the string in a sentence.

[CJ_London]

So you have a string

a,b,c,d

and you want

a,b,c and d

on your completed string try

left(mystr,instrrev(mystr,",")-1) & replace(mystr,","," and ",instrrev(mystr,","))

[Gina Maylone]

Thank you! We're getting there. Should I replace "mystr", with the actual name of my text string? The "and" is showing up properly, but I'm getting the item numbers instead of the text? Here's what I have right now (just the last few lines of code)

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
    'strList = Left$(strList, Len(strList) - 2)
    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

[CJ_London]

yes - replace mystring with the name of your string

not following your code, you build up a list with strList, then assign it to a manipulated servicetext

Perhaps it should be

ServiceText = Left(strList, InStrRev(strList, ",") - 2) & Replace(strList, ",", "and ", InStrRev(strLisrt, ","))

[Gina Maylone]

You are absolutely correct - I had a bunch of junk in there that I had tried to get to work and needed to clean it up. So this works great! Except if I unselect all but one, I get an "invalid procedure or call" error. Any thoughts on this?

  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(Me.ServiceText, InStrRev(Me.ServiceText, ",") - 1) & Replace(Me.ServiceText, ",", " and ", InStrRev(Me.ServiceText, ","))
       
      End If

[CJ_London]

you need to test for no comma's

if instr(ServiceText,"'")<>0 then ServiceText= Left(Me.ServiceText, InStrRev(Me.ServiceText, ",") - 1) & Replace(Me.ServiceText, ",", " and ", InStrRev(Me.ServiceText, ","))

[Gina Maylone]

Hi again Ajax, sorry to be a pain, but I've been working on this for the last couple of hours...the if statement for some reason eliminates the "and" and leaves me with just commas. If I remove the if statement, I get "and" and the error I was getting earlier "invalid procedure or call". I'm at a loss. Any other thoughts?

Thank you so much for your time!

[June7]

Maybe:

Left(strList, InStrRev(strList, ",") - 1) & " and " & Right(strList,1)

[ssanfu]

This is how I handled adding the "and" at the end.... they are unbound controls but the code should be adaptable.
Check all the check boxes, then uncheck the first check box.

[Gina Maylone]

THANK YOU ALL for your suggestions. I have been working, learning, working, learning about this whole instr (etc) business. Ssanfu, your example db is wonderful, so much code for a single line of text! My selections come from a list box however, so I cannot reference exact selections as you have. And am not bright enough to adapt it to what I'm doing.

Dim arChecks
    arChecks = Array("", "Adaptor", "Welding Flange", "Pipe", "Hanger")

June - your suggestion works great but I am stuck with a trailing "and". I am trying various RIGHT and LEFT combinations to be rid of it.

Again, I appreciate you all SO much! As the older I get, the less I know.

Have a fantastic day!
Gina

[CJ_London]

I don't think using arrays in this situation is the right thing to do - potentially a lot of maintenance. I've rewritten your createstring sub as follows

Sub CreateString()
Dim ctl As Control
Dim tmpstr As String
    
    tmpstr = ""
    For Each ctl In Me.Controls
        If ctl.Name Like "Checkbox*" Then
            If ctl = True Then tmpstr = tmpstr & ctl.Controls(0).Caption & ", "
        End If
    Next ctl
    
    If tmpstr <> "" Then
        tmpstr = Left(tmpstr, Len(tmpstr) - 2)
        Text18 = Left(tmpstr, InStrRev(tmpstr, ",") - 1) & " and " & Right(tmpstr, Len(tmpstr) - InStrRev(tmpstr, ","))
    End If
    
End Sub

just copy and paste to replace your current sub

This has the benefit if you add new checkboxes (so long as they are called checkbox1/2/3/7/8 etc and have an associated label then it takes the value from the label caption - which you can edit as required) without needing to change your code

[Gina Maylone]

Good morning Ajax!

Thanks for trying - but I'm not using checkboxes. I am using a list box which gets its values from another table.

[CJ_London]

- but I'm not using checkboxes. I am using a list box

so why didn't you supply that?

Good luck with your project