Need help with Query Critera

  • Thread starter Thread starter Guest
  • Start date Start date
G

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)
 
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"
 
JohnnyA said:
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)
Click to expand...


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
 
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)
Click to expand...

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]
 
Back
Top