Filter Report by values from two list box

[josejomonm]

I am having a list box where I will select the Plants (Plant-1 or
Plant-2 Etc.) or leave blank for selecting all plants. Another list box
in the same form where the supervisors list is provided. A report named
"Assigned Jobs" shall open by meeting all the following conditions

1) Both Plant list box and supr list box not selected
2) Plant list box selected and supr list box not selected
3) Plant list box not selected but supr list box selected
4) Both Plant and supr list boxes are selected

[Rick Brandt]

<jo********@yahoo.comwrote in message
news:11*********************@n51g2000cwc.googlegro ups.com...

>I am having a list box where I will select the Plants (Plant-1 or
Plant-2 Etc.) or leave blank for selecting all plants. Another list box
in the same form where the supervisors list is provided. A report named
"Assigned Jobs" shall open by meeting all the following conditions

1) Both Plant list box and supr list box not selected
2) Plant list box selected and supr list box not selected
3) Plant list box not selected but supr list box selected
4) Both Plant and supr list boxes are selected

In the query used by the report set criteria to...

WHERE (PlantField = Forms!FormName!PlantListBox
OR Forms!FormName!PlantListBox Is Null)
AND (SuprField = Forms!FormName!SuprListBox
OR Forms!FormName!SuprListBox Is Null)

This assumes that neither ListBox allows multiple selections.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Jose]

Thanks for your reply.

But My problem is that both of the list boxes may have multiple
selections.

[pietlinden]

jo********@yahoo.com wrote:

I am having a list box where I will select the Plants (Plant-1 or
Plant-2 Etc.) or leave blank for selecting all plants. Another list box
in the same form where the supervisors list is provided. A report named
"Assigned Jobs" shall open by meeting all the following conditions

1) Both Plant list box and supr list box not selected
2) Plant list box selected and supr list box not selected
3) Plant list box not selected but supr list box selected
4) Both Plant and supr list boxes are selected

So what's the big deal? You loop through the ItemsSelected collection
of the MSLB, create a filter, and pass it as a filter in the open event
of the report. If there is no selection, then that filter is not
built. Just test the value before you append it to the criteria list.
There's an example at AccessWeb...

http://www.mvps.org/access/reports/rpt0005.htm