Query Criteria Using IIf Statements

[MintChipMadness]

I am having issues with the criteria for a Query. What I am trying to do is if a control (sumcheck) is checked it filters the summary field with what is in the contents control or doesn't filter the field and if the sumcheck control is not checked it filters the DOC field with what is in the contents control or doesn't filter the field. When one iif statement is true the other will always be false. I listed below the controls in the form and the criteria in each field in the Report Query. Everything else works fine except when I added the criteria to both fields. Please let me know what would be the correct iif statement to put in each. Thank you for your help.


BoxesFilter Form

sumcheck control = True/False
contents control = text box

Report Query



DOC Field Critieria = IIf([Forms]![BoxesFilter]![sumcheck]=False,Like "*" & [Forms]![BoxesFilter]![contents] & "*")
Summary Field Criteria = IIf([Forms]![BoxesFilter]![sumcheck]=True,Like "*" & [Forms]![BoxesFilter]![contents] & "*")

[June7]

The IIf statements are missing the 'if false' value. However, don't think this will work regardless, seems I have tried.

What is nature of DOC and Summary fields - can record have data in both? Is the data of the same nature? If only one field can have data then just put the LIKE criteria (not inside IIf) under each field on separate OR rows. The checkboxes not needed.

[MintChipMadness]

Yes the records can have data in both. The user is choosing if they want to sort the Report by Summary or Contents (Hence checkbox) but cannot sort by both because they share the same control in the form. I could create two text box controls in the form and do what you suggested but I was hoping to have one control and have it decide to filter the DOC or Summary field by using the checkbox. I did use Like "*" as the 'if false' value but I believe that doesn't include null values which both fields in the query could have. What I would like the 'if false' to do is not apply the filter at all if it is false. Is there an expression for that?

[June7]

Right, the LIKE will not return records where field is null, which I guess suits your situation.

Create a field in query with expression:

FilterData: IIf([Forms]![BoxesFilter]![sumcheck]=True,[DOC],[Summary])

Criteria under that constructed field: Like "*" & [Forms]![BoxesFilter]![contents] & "*")

What prevents both checkboxes being simultaneously checked? Are they in an OptionGroup control? If they are then the OptionGroup control is the control that must be referenced in the IIf.

[MintChipMadness]

I think having a visual might help because I am not understanding your solution. I attached a test database. What should happen is if the check box is checked with nothing in the contents field, the report should show just the summary information in the report for all records. If there is something in the contents text box (Form), it would filter the summary information by what is in the contents. In addition, if the checkbox is not checked, and nothing is in the contents field it would show all the DOC field information in the report. If something is written in the contents text box, it would filter the DOC information by what is in the contents field. Hopefully that helps. Thank you.

[MintChipMadness]

If this is not possible is there a way to set the criteria for the query in VBA based on whether or not the user checks the box? I could put it on the on click event for the button that opens the report. I could then insert criteria into the field I want. I haven't done that before so I would need a little help with the code. Thank you again for all your help.

[MintChipMadness]

June,
your solution to add another field with an iif statement worked perfectly. I guess I skimmed over your post and didn't see the solution at first. Thank you for your help. Have a good weekend.