[RESOLVED] Listbox - trailing comma for multiple records

[soconnor]

I have the following code:

strSQL = strSQL & "WHERE MerchantID in ("
        For f = 0 To lstMerchants.ListCount - 1
        
        If lstMerchants.Selected(f) Then
                    strSQL = strSQL & (lstMerchants.ItemData(f)) & ","
            
            End If
        
        Next f
        strSQL = strSQL & ")"

At the moment, it lists merchants in a listbox and allows the user can select as many merchants as desired. It worked well in Access and there was no probs, but when trying against a SQL database the trailing comma throws the query. If the user selects one record, then no comma, and also shouldn't be a comma on the last selected merchant. I've tried SelCount but couldn't figure out the best way to use it.

I'm sure its simple but whats the best way to counteract this problem?

Thanks in advance!

[TysonLPrice]

This is what I use somewhere. Just always add a comma even for one selection and then get rid of it.

ilen = Len(strUpdateClause)
 strUpdateClause = Mid(strUpdateClause, 1, InStrRev(strUpdateClause, ",") - 1) & " " & _
 Mid(strUpdateClause, InStrRev(strUpdateClause, ",") + 1, ilen - InStrRev(strUpdateClause, ","))

[soconnor]

Excellent piece of code. Worked like a charm!

Thanks