By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Filter Report by values from two list box

P: n/a
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

Dec 31 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
<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
Dec 31 '06 #2

P: n/a

Thanks for your reply.

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

Jan 1 '07 #3

P: n/a

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

Jan 3 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.