Filter Report by values from two list box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

josejomonm@yahoo.com - 31 Dec 2006 07:25 GMT

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

Reply to this Message

Rick Brandt - 31 Dec 2006 13:12 GMT

>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
[quoted text clipped - 5 lines]
> 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.

Signature

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

Reply to this Message

Jose - 01 Jan 2007 12:32 GMT

Thanks for your reply.

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

Reply to this Message

pietlinden@hotmail.com - 03 Jan 2007 04:37 GMT

> 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
[quoted text clipped - 5 lines]
> 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

Reply to this Message