query for cascade combo boxes (1 Viewer)

[eugzl]

I create query for cascade combo boxes:

SELECT tblLocations.LocationID, tblLocations.FacilityID, tblLocations.BuildingID, tblLocations.WingID, tblLocations.FloorID, tblFacilities.Facility & " " & tblBuildings.Building & " " & tblWings.Wing & tblFloors.Floor & "-" & tblLocations.Room AS Location
FROM tblWings INNER JOIN (tblFloors INNER JOIN (tblFacilities INNER JOIN (tblBuildings INNER JOIN tblLocations ON tblBuildings.BuildingID = tblLocations.BuildingID) ON tblFacilities.FacilityID = tblLocations.FacilityID) ON tblFloors.FloorID = tblLocations.FloorID) ON tblWings.WingID = tblLocations.WingID
WHERE (((tblLocations.FacilityID)=[Forms]![fRequest]![cboFacility]) AND ((tblLocations.BuildingID)=[Forms]![fRequest]![cboBuilding]) AND ((tblLocations.WingID)=[Forms]![fRequest]![cboWing]) AND ((tblLocations.FloorID)=[Forms]![fRequest]![cboFloor]));

The criteria in this query include parameters: Facility, Building, Wing, Floor. How to modify the query if not all Facilities have Buildings with Wing? How to keep cascade boxes workable in case if will skipped Wing value?

 

[pbaldy]

How about:

Queries: Use a parameter to return all records if Null

 

[eugzl]

How about:

Queries: Use a parameter to return all records if Null

I mean. How create condition for certain Facilities and/or Buildings where parameter Wing should be ignore?
Thanks

 

[pbaldy]

Well, the technique above should "keep cascade boxes workable in case if will skipped Wing value". If you want some sort of dynamic method where some facilities need wing and some don't, you'll need to incorporate that into your data somehow. Personally I'd build the SQL in code, which would let you incorporate that logic easier (I suspect).

 

[Pat Hartman]

The simplest method is to use "dummy" values so that every facility has at least one "wing". Otherwise, you will need to change the joins from inner to LEFT