Query based on 1-4 combo boxes

[kblehman]

I have a rather simple query that will produce filtered results based on the user's selection in 1 to 4 separate combo boxes on a form. But I can't get it to do what I want it to do. Here are my vitals:

Form = frmQryAdHoc
City combo box = cboCity (from tblWARNData)
County combo box = cboCounty (from tblCounties)
MWA combo box = cboWMA (from tblLookUpData)

Query = qryAdHoc (source = tblWARNData)
CoCity column criteria = [Forms]![frmQryAdHoc]![cboCity]
CoCounty column criteria = [Forms]![frmQryAdHoc]![cboCounty]
CoMWA column criteria = [Forms]![frmQryAdHoc]![cboMWA]

The query works fine if I make selections in all 3 combo boxes. However, if I make a selection in only cboCity, the query returns no results because it doesn't find a matching record where the city = cboCity and the County and MWA fields are blank.

Here's how I want it to work:

If cboCity = Detroit and cboCounty = Wayne, then
Results = 40 records (where city = Detroit AND county = Wayne)

But if cboCounty = Wayne and cboCity & cboMWA are blank, then
Results = 120 records (where county = Wayne; cities & MWAs = various)

IOW, I want the query to filter only on the cbo's where a selection has been made by the user and ignore any empty cbo's. What statement do I need to enter in each cbo-related criteria field so the query filters only on the combo boxes where a selection has been made by the user?

Any help you can provide will be greatly appreciated. Thank you.

 

[Dennisk]

in the criteria enter the following

here is one example
[Forms]![frmQryAdHoc]![cboCounty] or [Forms]![frmQryAdHoc]![cboCounty] is null

when the query is saved an extra column is created for
[Forms]![frmQryAdHoc]![cboCounty] and the criteria is set to 'is null' however you will notice that it is in an 'OR' grid. After you have entered them all it may look a bit messy.

 

[EMP]

See Jon K's basic criteria format and his Note (1) in this thread:-

http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[kblehman]

here is one example
[Forms]![frmQryAdHoc]![cboCounty] or [Forms]![frmQryAdHoc]![cboCounty] is null

Dennisk,
That statement worked perfectly. I entered it into the criteria field of each of the cbo-related items and saved the query. Now I can enter any number of items in the combo boxes and it will filter the query based on those entries. Thank you!