Multi-Selection List Box

[Bruno]

Any luck finding an answer to this? I'm trying to do the
same thing. I know that you need to first loop through
the list box and create a string containing all selected
values with some sort of delimeter like "," or in my case
below, I'm inserting repeated pieces of the WHERE
clause. For example:

For Each varItem In Me.MYLISTBOX.ItemsSelected

strCriteria = strCriteria & "table.column = " & Chr(34) &
Me.MYLISTBOX.ItemData(varItem) & Chr(34) & " OR "

Next varItem

Then I remove the trailing " OR " from the string as
follows:

If Len(strCriteria) > 0 Then

strCriteria = Left(strCriteriaAppt, Len
(strCriteriaAppt) - 3)

End If
___

This gives you a string with the selected values ready
for referencing in a query's WHERE clause, but I haven't
been successful in passing it as a parameter into a saved
query. I tried assigning the entire string to a text
control on a form (e.g., txtCriteria = strCriteria) , and
then referring to the text control in the saved query
criteria field (e.g., Forms!frmName!txtCriteria), but
that didn't work. If you know how, or found another
route I'd appreciate any advice.

Bruno

 

[John Vinson]

For Each varItem In Me.MYLISTBOX.ItemsSelected

strCriteria = strCriteria & "table.column = " & Chr(34) &
Me.MYLISTBOX.ItemData(varItem) & Chr(34) & " OR "

Next varItem

Try using the IN() operator instead: it will build a smaller and more
efficient SQL string.

strCriteria = "table.column IN("
For Each varItem In Me.MYLISTBOX.ItemsSelected

strCriteria = strCriteria & Chr(34) & _
Me.MYLISTBOX.ItemData(varItem) & Chr(34) & ", "

Next varItem

then trim off the final comma and replace it with a ).


Be sure to check the final strCriteria to make sure that it's
syntactially correct - blanks are important and easy to leave out or
insert in the wrong place!