asked on Multiple List box selection
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) & "','"
Next varItem
If strCriteria = "'" Then
SQL_Criteria = "Like '*'"
Else
SQL_Criteria = "IN(" & Left(strCriteria, Len(strCriteria) - 2) & ")"
End If
End Function
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.
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) & "','"
Next varItem
If strCriteria = "'" Then
SQL_Criteria = "Like '*'"
Else
SQL_Criteria = "IN(" & Left(strCriteria, Len(strCriteria) - 2) & ")"
End If
End Function
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.
Microsoft Access
Last Comment
simpkinst
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
The appropriate way to close this would be to post your solution and then select that as the answer.
ASKER
I noticed that the querydef was adding in an "=" sign to the code so I figured out that I had to build the SQL myself as noted above, I had not seen this answer by the time I had figured it out. Thank you, for your help.
ASKER
Thanks for your help.