Add checkbox parameter to query

[bigalpha]

I've been messing with this all day and can't figure this out.

I am using the Multi Search from that's available in the template forum, and currently it's working perfectly. I am trying to add one checkbox (chkIncludeManifests) to the form to do some filtering.

When the box is checked, I should search all records. When the box is unchecked, I should search only records with no manifest number [manifestnum].

I've tried setting up an IIF statement to filter out the values that are empty but it's not working.

 

[mbamber]

Try

Like Iif(chkIncludeMadnifests = True,"*",is null)

in the criteria for manifestnum

 

[Galaxiom]

WHERE Not Forms!formname.chkIncludeManifests And manifestnum Is Null
OR Forms!formname.chkIncludeManifests

 

[bigalpha]

Try in the criteria for manifestnum

Tried this and it didn't work, but the solution provided by Galaxiom works. Took some fiddling for me to get the logic right, though.

 

[bigalpha]

WHERE Not Forms!formname.chkIncludeManifests And manifestnum Is Null
OR Forms!formname.chkIncludeManifests

Hey, this worked like a champ. Took me a while to figure out the logic in my query but after I did, I was able to get it all buttoned up. Thanks a million.

SELECT tblManifestData.manifestnum AS [Manifest Num], CurrentCY.accumulationstartdate AS ASD, CurrentCY.Pounds, tblGeneratorDodaac.GeneratorDodaac AS [Gen DODAAC], tblEtidDodaac.EtidDodaac AS [ETID DODAAC], CurrentCY.ETIDDocNum AS [ETID Doc], CurrentCY.generatordocnum AS [Gen Doc Num], CurrentCY.generator, CurrentCY.WasteDescription AS [Waste Description], CurrentCY.CurrentCYIDPK
FROM tblManifestData RIGHT JOIN (tblGeneratorDodaac RIGHT JOIN (tblEtidDodaac RIGHT JOIN CurrentCY ON tblEtidDodaac.EtidDodaacIDPK = CurrentCY.EtidDodaacIDFK) ON tblGeneratorDodaac.GeneratorDodaacIDPK = CurrentCY.GeneratorDodaacIDFK) ON tblManifestData.ManifestDataIDPK = CurrentCY.ManifestDataIDFK
WHERE (((tblManifestData.manifestnum) Is Null) AND ((CurrentCY.accumulationstartdate) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])=False)) OR (((CurrentCY.accumulationstartdate) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])<>False)) OR (((tblManifestData.manifestnum) Is Null) AND ((CurrentCY.Pounds) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])=False)) OR (((CurrentCY.Pounds) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])<>False)) OR (((tblManifestData.manifestnum) Is Null) AND ((tblGeneratorDodaac.GeneratorDodaac) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])=False)) OR (((tblGeneratorDodaac.GeneratorDodaac) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])<>False)) OR (((tblManifestData.manifestnum) Is Null) AND ((tblEtidDodaac.EtidDodaac) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])=False)) OR (((tblEtidDodaac.EtidDodaac) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])<>False)) OR (((tblManifestData.manifestnum) Is Null) AND ((CurrentCY.ETIDDocNum) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])=False)) OR (((CurrentCY.ETIDDocNum) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])<>False)) OR (((tblManifestData.manifestnum) Is Null) AND ((CurrentCY.generator) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])=False)) OR (((CurrentCY.generator) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])<>False)) OR (((tblManifestData.manifestnum) Is Null) AND ((CurrentCY.WasteDescription) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])=False)) OR (((CurrentCY.WasteDescription) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])<>False)) OR (((tblManifestData.manifestnum) Is Null And (tblManifestData.manifestnum) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])=False)) OR (((tblManifestData.manifestnum) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])<>False)) OR (((tblManifestData.manifestnum) Is Null) AND ((CurrentCY.generatordocnum) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])=False)) OR (((CurrentCY.generatordocnum) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*") AND (([forms]![frm_searchmulti].[chkincludemanifests])<>False));