Getting a string from a multi select list box.

[greymonkey]

I have a multi select list box listing client’s names and I need to get the selected names into the criteria for a query.

If you have any ideas or sample code which would sort this problem it would help me a lot.

Cheers,

 

[FancyPrairie]

This should do it for you:

Note that it assumes that you are looking for a string (not a number)

    Dim strSQL as string
    Dim strSQLWhere as string
    dim varItem as variant
   
   strSQL = "Select * From YourTable"
   strSQLWhere = vbNullString
   
    If (YourListBox.ItemsSelected.Count > 0) then
        strSQLWhere = " WHERE "
        For Each varItem In YourListBox.ItemsSelected
            strSQLWhere = strSQLWhere & "YourFieldName ='" & lst.column(0,varItem) & "' OR "
        Next varItem

        strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - 4)  'Get rid of last OR
    End If

   strSQL = strSQL & strSQLWhere

   Set dbs = CurrentDb
   dbs.QueryDefs.Delete "YourQueryName"
   Set qdf = dbs.CreateQueryDef("YourQueryName",strSQL)
   strSQL  = vbNullString
   DoEvents

ExitProcedure:
    Exit Sub

ErrHandler:

    If (Err.Number = 3265) Then         'IFT, tryed to delete a query that did not exist.
        
        Resume Next

    Else

        Msgbox err.number & err.description
        Resume ExitProcedure
    End if

End Sub