IIF statement in criteria not working

[hollyylloh]

This sure seems like it should be simple but I have stared at it so long...

I have a query that checks a check box on a form.

If the check box is NOT checked then the query should return all records
except those that are null.

If the check box IS checked the query should return all the records that
equal the vaule in the field on the form named cmbProject.

The part that does not work is the first part. It currently returns no
records.

For Field: Project, Table: qryStatusReport1

IIf([Forms]![frmrptGeneralReportStatus]![chkProject]=False,[qryStatusReport1].[Project] Is Not Null,[Forms]![frmrptGeneralReportStatus]![cmbProject])

I hope you can help.

 

[hollyylloh]

Thank you Ken I was able to make this work. Thank you also for the explanation.

Rather than calling the IIF function just build the Boolean logic into the
query's WHERE clause. It might be a little tricky to do this in design view,
so I'd recommend doing it in SQL view:

SELECT *
FROM qryStatusReport1
WHERE
([Forms]![frmrptGeneralReportStatus]![chkProject]
AND Project = [Forms]![frmrptGeneralReportStatus]![cmbProject])
OR
(NOT [Forms]![frmrptGeneralReportStatus]![chkProject]
AND Project IS NOT NULL);

You'll see that the first parenthesised expression will evaluate to TRUE for
each row where the check box is checked and value in the Project column
equals that in the cmbProject control on the form. The second parenthesised
expression evaluates to TRUE for each row if the check box is not checked and
the value in the project column is not Null. Parenthesising the expressions
forces each to evaluate independently of the other, and by applying a Boolean
OR operation to the two expressions a row will be returned where one of them
evaluates to TRUE. Both cannot evaluate to TRUE simultaneously of course as
the check box is either TRUE or FALSE for every row, but both can evaluate to
FALSE depending on the value in the Project column in the row, in which case
the row would not be returned.

BTW I'd suggest that you save the query in SQL view as if you switch to
design view Access will move things around. The underlying logic will be the
same and it will give the same results, but it will be more difficult to read
the logic behind the query than as written in SQL view.

Ken Sheridan
Stafford, England

This sure seems like it should be simple but I have stared at it so long...

I have a query that checks a check box on a form.

If the check box is NOT checked then the query should return all records
except those that are null.

If the check box IS checked the query should return all the records that
equal the vaule in the field on the form named cmbProject.

The part that does not work is the first part. It currently returns no
records.

For Field: Project, Table: qryStatusReport1

IIf([Forms]![frmrptGeneralReportStatus]![chkProject]=False,[qryStatusReport1].[Project] Is Not Null,[Forms]![frmrptGeneralReportStatus]![cmbProject])

I hope you can help.