Multiple Criteria in Query

[wtrimble]

I need to have a query that filters multiple inputs from a user on a form. There are three "combo boxes" on the form but they all may not be filled. How can I format the criteria for three different fields to filter the data if an input is chosen, but not to filter if it is left blank??

Thanks

 

[David Eagar]

Try here http://www.access-programmers.co.uk/forums/showthread.php?t=177560 I was trying the same thing athis is a working solution

 

[wtrimble]

I tried:

IIf(IsNull([Forms]![frmCatSrch]![Combo11]),"",[Forms]![frmCatSrch]![Combo11])

for the criteria in the query but the query is still blank when nothing is filled in "combo 11". I want all records to show unless an input in "combo 11" is selected. Any thoughts?

 

[David Eagar]

In AfterUpdate event for the combobox add If IsNull([combo11]) Then: [combo11] = "*"

 

[wtrimble]

The combo box may not be touched though so Afterupdate would not be activated each time

 

[David Eagar]

For each of the three comboboxes, on formload [comboxx]="*"
So if you make a selection on 1 combobox, the other two will be "*"
If you then clear the first combox and select another one, the same process will occur

 

[wtrimble]

Yes, my problem is that the query reads "" as an input and won't ignore that criteria. It's filtering out the "" in the column and comes up empty because there are none. I need to ignore the criteria all together some how

 

[David Eagar]

Yes, my problem is that the query reads "" as an input and won't ignore that criteria.

Where does it get "" from? It should either be "*" or the value selected from the combo box

 

[wtrimble]

Right sorry, I meant "*". But still if it is "*" no records will show.

 

[David Eagar]

?? "*" all records should show
Can you post the database?

 

[wtrimble]

Not sure how to post database... but if I put like "*" in the criteria for a field, all records will show. If i put [combo11]="*" on page load or for default value, the query remains blank. So I need an Iff() that will test if "IsNull(combo11)" if true display like "*" , if false: [combo11] . But how I'm not sure....

 

[wtrimble]

Got it. If I put:

1:2:3:4:5:select * from yourtablewhere (yourcolumn1 = [Forms]![frmCatSrch]![Combo11] or [Forms]![frmCatSrch]![Combo11] is null) and (yourcolumn2 = [Forms]![frmCatSrch]![Combo12] or [Forms]![frmCatSrch
and it worked. Thanks for the help

 

[wtrimble]

Sorry. The SQL code was:

select *
from yourtable
where     (yourcolumn1 = [Forms]![frmCatSrch]![Combo11] or [Forms]![frmCatSrch]![Combo11] is null) and    (yourcolumn2 = [Forms]![frmCatSrch]![Combo12] or [Forms]![frmCatSrch]![Combo12] is null)