multiple filters

[david wampler]

Hello, I have a form that I would like to filter by a number of options, like equiment type, system, manufacturer. How can I have Filter selection combo boxes and then activate the filter by clicking a filter button? One other issue with this is that I want to have the filter selection combo boxes only contain the selctable information once. I mean that if I link the combo boxes directly to a column in my table that contains several thousand records that contain the same info the combo box will drop down with a selection for each record.

Any ideas

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

 

[golfinray]

Allen Browne at www.allenbrowne.com and Martin Greene at www.fontstuff.com
both have excellent examples of cascading combo boxes for filtering.

 

[Al Campagna]

David,
Given that many criteria, it might be best to build a filter "dialog"
form,
with combos for each criteria.

Use those values to filter the criteria in the query behind the form.
Ordinarily, you would criteria a query field named City with...
= Forms!MyFilterFormName!cboCity
But you may want to view ALL cities, if the combo is left blank... so...
= Forms!MyFilterFormName!cboCity & "*"

A button on that form would be used to open the form when all the user
criteria are entered.
The data form's query will use the dialog form's values to deliver the
correct recordset.

To prevent dupes in the combos, use a Totals query as the RowSource,
and group on your field.
You may also add...
Is Not Null
to that field's criteria, to prevent a blank combo selection.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Douglas J. Steele]

If you're going to use a wildcard, you need to use Like, not =

Like Forms!MyFilterFormName!cboCity & "*"

I actually prefer to use

= Forms!MyFilterFormName!cboCity Or (Forms!MyFilterFormName!cboCity IS NULL)

 

[Al Campagna]

Douglas,
Of course... you're right.
Totally missed that!
Thanks,
Al