Query criteria to return Null

[music_al]

I have a search form with 2 combo boxes, the second combo box is a nested combo box of the first. In the top combo the user selects fruit, meat, or pastry. In the second combo box the user would select types of fruit or meat etc.

I have a search button that changes the record source of the query depending on if anything was entered in the second combo box. The results will return suppliers of fruit for example but if something was entered in the second combo box it will return suppliers of specific fruit.

I am currently doing this with two queries but I want to do it with just one query.

I have set the criteria for the second combo box as follows but it doesn’t seem to be working.

IIf(IsNull([Forms]![frm_Product_Supplier_Search]![cbo_Category3]),Null,[Forms]![frm_Product_Supplier_Search]![cbo_Category3])

So effectively, if there is something in the second drop down box I want the query to use it or if it is empty I want the query to ignore it as though there was nothing in there.

 

[pbaldy]

Try this:

http://theaccessweb.com/queries/qry0001.htm

 

[music_al]

If the query criteria is blank I don’t want it to return all the records, I just want the query to use the other criteria.

So, the combo box that would hold the variety of food types is blank so return the results of whatever is set in the other combo box, I.e. fruit suppliers.

 

[Mark_]

Please let me know if the following is the logic you want.
IF both comboboxes are blank, show everything.
If combobox 1 has a value, limit table based on Combobox 1.
If combobox 2 has a value, limit table based on both comboboxes.

LIMIT. If combobox 2 has a value and combobox 1 is changed, you also need to clear combobox 2.

Does this about sum up what you are trying to do?

If so, I'd add a procedure that creates your filter and have it called after each combobox is filled in. Also the best place to set combobox 2 to null if combobox 1 changes.

 

[music_al]

Hi pbaldy

My apologies. I read your reply and I realised how it made sense. That works fine.

Thank you very much


Al

 

[pbaldy]

Hi pbaldy

My apologies. I read your reply and I realised how it made sense. That works fine.

Thank you very much


Al

Happy to help!