Multi criteria combo query problem (1 Viewer)

compton clerk

New member
Local time
Today, 05:21
Joined
Aug 3, 2014
Messages
2
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 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.
 

compton clerk

New member
Local time
Today, 05:21
Joined
Aug 3, 2014
Messages
2
Thanks Uncle Gizmo.

Lots of info at the link. I will have a good look and see if it can be adapted to produce what I need. It looks hopeful.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:21
Joined
Jul 9, 2003
Messages
16,245
This one has got the login problem Uncle T.


I made the site Private again....

I have 4000 plus members and growing at about 2 or 3 a week... Sometimes 2 or 3 a day!

It's my retirement plan, but I haven't worked out how to make any money from it yet. (Seeing a Guy tomorrow)
 

vbaInet

AWF VIP
Local time
Today, 05:21
Joined
Jan 22, 2010
Messages
26,374
@Uncle T: I didn't know it was the entire content that got locked down, I thought you could unlock individual threads, but it makes sense now why it keeps doing that.
 

Users who are viewing this thread

Top Bottom