Multiselect listbox problem in queries

[M. Nguyen]

I tried to create a query with a criteria based on the multiselect string
listbox on the form. I created a function in VBA to get selected values in
the listbox, and concatenated those values into one string separated by
quotes and commas as needed. Then, I put the function name in the query
criteria using the IN(functionname()) criteria expression. However, it
looked like Access only recognized the whole string as one value. How can I
make Access recognize multiple values in one string?

 

[S.Clark]

Given that the syntax of the IN clause is: WHERE FieldName IN ("a", "b", "c")

The function to build the string would need to return a string that contains
either the double quote or apostrophes around each value.

dim qt as string
qt = Chr$(34)

'...some procesing...

if len(strVal) > 0 then
strRetVal = strVal & ", " qt & strVal & qt
end if

 

[M. Nguyen]

This is the function that I used for the query criteria in conjunction with
the IN clause. My Access version is 2002.

Function SelectedItem() As String
SelectedItem = Chr(34) & "11390" & Chr(34) & ", " & Chr(34) & "11391" &
Chr(34)
End Function

I created this string in place of the multi-select listbox to do the test
before I actually use the listbox. This string contains double quotes around
each value followed by a comma. However, Access still can't recognize two
values in this string when I put this criteria in the query:
IN(SelectedItem())