Hi.
I hope someone may be able to help with query multi-criteria problem I am so far unable to overcome.


My database is for recording the decorations and contents of apartments and houses.

Each apartment or house has an address. Within each address there are Areas (rooms) and Parts (parts of an Area which are then described)


On my main form there is an address ID field


I have two combos to search / filter the current address data which returns the search / filter on a data entry sub form.

Both combos return information from the same table (Inventory)


I have one combo where an area can be selected via a query. (Works ok) (cboArea)


The second combo (cboPart) displays Parts (parts of the pre-selected Area) This is the problem query.

The second combo query (based on the choice in the first combo) successfully returns only applicable parts for the area previously selected in the first combo for the current address.

Problem. It is convenient within the concepts of the database to be able to click the second combo without making a choice from the first combo as in this circumstance all the 'Parts' from the searched table are displayed. This, for example, is so I can work on (have returned) all of the ceilings or walls or floor coverings etc in all of the current address Areas (rooms)

However the second combo used without making a choice in the first combo returns all 'Parts' for all addresses in the table being searched regardless of the current property address.

My question is:

Is there a criteria for the second combo I can add which will only return all Parts for the current address when nothing is selected from the first combo but still allow the current filtering when a choice is made from the first combo.

Here is the SQL statement in question for the second combo


SELECT DISTINCT Inventory.Part, Inventory.AddressID, [AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null AS Expr1
FROM Inventory
WHERE ((([AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null)=True) AND

((Inventory.Area)=[Forms]![InventoryReport]![cboArea])) OR ((([Forms]![InventoryReport]![cboArea]) Is Null))
ORDER BY Inventory.Part;



i would like to learn how to express the following for combo two:

If an area choice is selected from combo one (cboArea) display all the Parts in the table for that area filtered by the current address ID
If an area choice is not made from combo one (cboArea) combo two to return all the parts in the table but only for the current address.

Apologies for being long winded.
Hoping someone may be interested to help.