Multiple Check Box Query Problem 1

[ChrisJF]

Hi all,

I have a problem which I can't get my head around.
I have a form which has 5 check boxes on it.
cbxNetwork
cbxSystemOperation
cbxCommercial
cbxEngineering
cbxBusinessResources

The check boxes are not currently bound to the underlying table/query.

I have a table with other fields but also containing 5 corresponding fields (Network;SystemOperation;Commercial;Engineering;BusinessResources) with a data type Yes/No.

What I want to do is to create a query which outputs everything if none of the forms check boxes are touched, and produce a selection if one or more of the forms check boxes are checked.

i.e nothing checked = all results
Scenario : chxNetwork selected, only records with Network checked returned
Scenario : chxNetwork and chxCommercial checked on form, records output which have Network only checked, Commercial only checked and Network & Commercial checked.

etc.. up to 5 check boxes checked.

I have been trying to use the nz() function but cannot produce the correct result.

Thanks
ChrisJF


"He who is not aware of his ignorance will be only misled by his knowledge." - Richard Whatley

 

[scriverb]

Try this:
Select A.*
FROM tblYourTableName as A
WHERE A.Network = IIF(FORMS![frmYourForm]![cbxNetwork],True, A.Network) or A.SystemOperation = IIF(FORMS![frmYourForm]![cbxSystemOperation],True, A.SystemOperation) or A.Commercial = IIF(FORMS![frmYourForm]![cbxCommercial],True, A.Commercial) or A.Engineering = IIF(FORMS![frmYourForm]![cbxEngineering],True, A.Engineering) or A.BusinessResources = IIF(FORMS![frmYourForm]![cbxBusinessResources],True, A.BusinessResources);

Post back if you have any problems with this. I had no tables to test it on so we will have to run it at your site. Update the red code with the table name and the form name.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ChrisJF]

scriverb,

Thanks for the code, but it only seems to work if all boxes are checked, returning a dynaset with only records where at least 1 box is checked in the table, or if none are selected, all table results are returned.
When you select a combination of check boxes, the query still returns the full table records.

I can email you the table, query and form if you want.

Also, something odd seems to happen intermittently to the check boxes on the form, they seem to lock occasionally but the properties on the check box still indicates unlocked, and the only way I seem to be able to get round it is saving the properties to locked and then save them back to unlocked.

Regards
ChrisJF

"He who is not aware of his ignorance will be only misled by his knowledge." - Richard Whatley

 

[scriverb]

ChrisJF: Go ahead and send the database and I will take a look at it. I will return it with the fix and also post the corrected answer here for others to see.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

I received you database and here is the final SQL that works with your data.
SELECT A.*, A.CorporateDevelopmentCompliance
FROM [NEM Obligations & Procedures] AS A
WHERE ((A.NetworkCompliance=IIf([FORMS]![BU to Comply]![cbxNetwork]=True,True,Null)) Or (A.SystemOperationsCompliance=IIf([FORMS]![BU to Comply]![cbxSystemOperations]=True,True,Null)) Or (A.CommercialCompliance=IIf([FORMS]![BU to Comply]![cbxCommercial]=True,True,Null)) Or (A.EngineeringCompliance=IIf([FORMS]![BU to Comply]![cbxEngineering]=True,True,Null)) Or (A.BusinessResourcesCompliance=IIf([FORMS]![BU to Comply]![cbxBusResources]=True,True,Null)) Or (A.CorporateDevelopmentCompliance=IIf([FORMS]![BU to Comply]![cbxCorporateDev]=True,True,Null))) OR ( True = IIF(FORMS![BU to Comply]![cbxNetwork]=False and FORMS![BU to Comply]![cbxSystemOperations]=False and FORMS![BU to Comply]![cbxCommercial]=False and FORMS![BU to Comply]![cbxEngineering]=False and FORMS![BU to Comply]![cbxBusResources]=False and FORMS![BU to Comply]![cbxCorporateDev]=False, True, False));

Post back if you have any further questions.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???