Multi-Select Listbox as Paramater for Query

[MJatAflac]

I have a form that currently uses a combo box to pass a paramater to a query
that ir run when the user clicks a button. The users would like to be able to
run this query for multiple selections at one time.

I changed the combo-box to a list box and changed the multi select property
to simple but now I don't know how to refer to it in my query. When I use an
equal sign it works fine as long as the user has only selected one item in
the list box. Using In doesn't seem to work at all.

 

[Allen Browne]

You cannot use a multi-select list box name directly in a query.

You will need to programmatically loop through the ItemsSelected collection
of the list box to build up the string to use in the WHERE clause of the
query. Typically this means building the entire SQL statement in code, or
just building the WHERE clause to use in the WhereCondition of OpenReport or
in the Filter of a form.

For an example of how to write that code, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

 

[Paul Overway]

You can either build the SQL or WHERE clause programattically, or...

use the following technique which is based on a hidden text box for the
query parameter [commadelimitedstring]. After the user has made selections
from the list box, you'd use a function like GetItemsSelected (see below)
to obtain a comma delimited list and assign it to the text box used as a
parameter in the query, i.e.,

Sub lstSomeListBox_AfterUpdate()

Me.txtSomeTextBox = GetItemsSelected(Me.lstSomeListbox,False)

End Sub


Then add a column in the query that executes the function SafeEvalIn (see
below)

SafeEvalIn([SomeField],[TheTextBoxWithTheCDL])

Then set the criteria to True or False, depending on whether you want to
include or exclude records where the field that matches a value in the list.

'Place these functions in a public module
Function GetItemsSelected(ctl As ListBox, IncludeQuotes As Boolean) As
Variant

Dim varThing As Variant

On Error Resume Next

For Each varThing In ctl.ItemsSelected
If IncludeQuotes Then
'Need quotes for fields containing a string
GetItemsSelected = GetItemsSelected & Chr(39) &
ctl.ItemData(varThing) & Chr(39) & ","
Else
GetItemsSelected = GetItemsSelected & ctl.ItemData(varThing) &
","
End If
Next

If Len(GetItemsSelected) > 0 Then
GetItemsSelected = Left(GetItemsSelected, Len(GetItemsSelected) - 1)
Else
GetItemsSelected = Null
End If

End Function

Function SafeEvalIn(FieldValue As Variant, CDL As Variant) As Boolean
'Jet SandBox limits use of Eval in queries, so, use this instead

On Error Resume Next

If IsNull(FieldValue) Or IsNull(CDL) Or Len(CDL)=0 Or Len(FieldValue)=0
Then
SafeEvalIn = False
Else
SafeEvalIn = Eval(FieldValue & "In(" & CDL & ")")
End If

End Function