Need help with Query Critera

[Guest]

I'm trying to use the following IIF in a field's creteria within a Query.
The Is Null or Not Is Null doesn't work. I don't get an error message, just
get zero results

IIf([forms]![frmBRNSummary]![ftrBranchStatus]="Open",Is Null,Not Is Null)

 

[Steve Schapel]

Johnny,

It's not really valid to try and use an IIf() function to try and return
a value that will be evaluated as a query criterion.

Try writing the criteria on two lines, like this...
Is Null And [Forms]![frmBRNSummary]![ftrBranchStatus]="Open
Is Not Null And [Forms]![frmBRNSummary]![ftrBranchStatus]<>"Open"

 

[Marshall Barton]

I'm trying to use the following IIF in a field's creteria within a Query.
The Is Null or Not Is Null doesn't work. I don't get an error message, just
get zero results

IIf([forms]![frmBRNSummary]![ftrBranchStatus]="Open",Is Null,Not Is Null)

You can only use values conditionally, not sytax elements.

Set the criteria of any field that doesn't already have a
criteria to:

(thefield Is Null) = (Forms!frmBRNSummary!ftrBranchStatus =
"Open")

All on one line regardless of how these newsgroups mangle
the line

 

[John Vinson]

I'm trying to use the following IIF in a field's creteria within a Query.
The Is Null or Not Is Null doesn't work. I don't get an error message, just
get zero results

IIf([forms]![frmBRNSummary]![ftrBranchStatus]="Open",Is Null,Not Is Null)

Try instead a criterion of

([Forms]![frmBRNSummary]![ftrBranchStatus] = "Open" AND [fieldname] IS
NULL)
OR
([Forms]![frmBRNSummary]![ftrBranchStatus] <> "Open" AND [fieldname]
IS NOT NULL)

The problem is that you can only pass *values* - not operators such as
NOT - using an IIF statement.

John W. Vinson[MVP]

 

[John Vinson]

(thefield Is Null) = (Forms!frmBRNSummary!ftrBranchStatus =
"Open")

<boing!>

Elegant, Marshall. Filing that idea away for future use!

John W. Vinson[MVP]