The expression "[Forms]![frmActivity]![processor list]" (note the exclamation points, not periods) obtains the Value property of the "processor list" control.
For a non-Multiselect list box, the Value property is the selected item, so your criteria comes out as
Like 'item1' when item1 is selected. That will work fine.
For a Multiselect list box, though, the Value property is always Null, so your criteria comes out as something like
Like Null, which never matches anything.
You can't use
Like to match a list of items; you need to use
In instead. What you would like to do is change the criteria to come out as
In ('a','b','c'...) where a, b, c, etc. are the text of the selected items.
Unfortunately, you can't do that by referring to the form in a criteria VBA expression. The
In operator is unique in that it takes a list of strings as its argument, and "list of strings" is not a data type that VBA understands. You could use VBA expressions for the individual items in the list, but then you'd have to require that number of items to be selected each time, which would be no good for this application.
The only way I can think of to get this to work using a query, is to set your criteria to a user-defined function that takes the value of the Processor column for the current row, and returns whether a match is selected in the list box. Add this column to the QBE grid:
Code:
Field: Dummy1: True
Show: not checked
Criteria: IsSelected([Processor])
where "Processor" is the name of the column your
Like criteria was applied to.
Then create this function:
Code:
Public Function IsSelected(Value) As Boolean
Dim v As Variant, lst As ListBox
Set lst = Forms!frmActivity![processor list]
** If lst.ItemsSelected.Count = 0 Then
** IsSelected = True
** Exit Function
** End If
For Each v In lst.ItemsSelected
If Value = lst.ItemData(v) Then
IsSelected = True
Exit Function
End If
Next v
IsSelected = False
End Function
(The lines marked "**" provide that, if no items are selected in the list box, it's as if
all of the items are selected. If you omit them, and no items are selected, the report will come out blank.)
This does work (I tried it), but it has a big disadvantage: The query must call this function for each row in its entire input recordset. The function has to find the list box and scan through the selected items to see if there's a match for the Processor column. Some of this--especially finding the list box--is relatively slow, and since it has to be done for
each record (including the ones that are not wanted on the report), it could really cause your query to run slow. It will be even worse if you want two or more Multiselect list boxes to match on.
It does work, though, so if your query source isn't too large it might perform acceptably.
As I said, this is the only way I can see it working
using a query. A better way, certainly a faster one, would be to have the form construct a criteria string itself (in code) from its own control values. For Multiselect list boxes, it would create a fragment like "[Field] In ('a','b','c'...)". For multiple list boxes, it would combine the fragments with AND operators. When it opens the report, it simply specifies the constructed criteria string as the "where condition" argument to OpenForm. The query wouldn't need any criteria in this case. (Technically, you wouldn't even need the query any more.)
If you don't already know how to use code in Access applications, this might be a good time to learn. Rick Sprague
