Multiple List box selection
Posted on 2016-08-02
I have a multi select list box on a form with a pretty intensive select query and I would like to use what the user selects as the criteria for the intensive query. I have a function that gets the information correctly but when it returns the selection to the query, it is like it returns nothing or something that the query doesn't like. Below you will see the function:
Function SQL_Criteria() As String
Dim varItem As Variant
Dim strCriteria As String
Dim ctrl As Control
Set ctrl = [Forms]![frmMain1].MPN
strCriteria = "'"
For Each varItem In ctrl.ItemsSelected
strCriteria = strCriteria + ctrl.Column(0, varItem) & "','"
If strCriteria = "'" Then
SQL_Criteria = "Like '*'"
SQL_Criteria = "IN(" & Left(strCriteria, Len(strCriteria) - 2) & ")"
I put the call to this function in my where clause, but it doesn't seem to run correctly. It doesn't give me an error just an empty table.
Thanks in advance for all your help.