If you want to check multiple values being true, you must use the AND operator
and not the & (concatenate) operator.
Expr1: IIf([LatestOccurenceWarning]="0" AND [LatestTardyWarning]="0" AND
[LatestMissedPunchesWarning]="0" AND [LatestNoCallNoShowWarning]="0" ,"NO","YES")
Also if those fields are number fields REMOVE the quote marks around the
zeroes. If the fields are blank (null or zero-length string (ZLS) then try
something like the following.
IIf([LatestOccurenceWarning] & "" ="" AND [LatestTardyWarning] & "" ="" AND
[LatestMissedPunchesWarning] & "" ="" AND [LatestNoCallNoShowWarning] & "" =""
,"NO","YES")
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Sorry it took me so long to get back to this.
I ran the query, and it returned the same result as my iif statement. Then,
I tried to fix the iif statement by doing this:
Expr1: IIf(([LatestOccurenceWarning]="0") & ([LatestTardyWarning]="0") &
([LatestMissedPunchesWarning]="0") &
([LatestNoCallNoShowWarning]="0"),"NO","YES")
I also tried setting everything equal to " ". Both of these tries gave me
the exact same result. What else can I try to get this to work????
Thanks!!!
KARL DEWEY said:
You stated the criteria as ALL fields null.
Do you have fields that are 'zero length'? If you erase data from a text
field it is not null but is zero length.
Try running this query --
SELECT [LatestOccurenceWarning], [LatestTardyWarning],
[LatestMissedPunchesWarning], [LatestNoCallNoShowWarning],
IIf([LatestOccurenceWarning] Is Null AND [LatestTardyWarning] Is Null AND
[LatestMissedPunchesWarning] Is Null AND [LatestNoCallNoShowWarning],
"NO","YES") AS Expr1
FROM YourTable;
Compare the displayed fields to Expr1.
--
Build a little, test a little.
:
I am still getting the same result: are there any troubleshooting tips you
can give me as to why it would not return a record whose
[LatestOccurenceWarning] and [LatestNoCallNoShowWarning] were NOT NULL, while
the other fields were null??
Thanks!!
:
Yep, '&' is not a logical function.
Expr1: IIf([LatestOccurenceWarning] Is Null AND [LatestTardyWarning] Is Null
AND [LatestMissedPunchesWarning] Is Null AND [LatestNoCallNoShowWarning],
"NO","YES")
--
Build a little, test a little.
:
I am trying to have a field in a query that states if all four specified
fields are null, return "NO", else return "YES". Here is what I put into the
expression builder:
Expr1: IIf(IsNull([LatestOccurenceWarning]) & IsNull([LatestTardyWarning]) &
IsNull([LatestMissedPunchesWarning]) &
IsNull([LatestNoCallNoShowWarning]),"NO","YES")
I am sure I am missing something very elementary - it currently returns "NO"
for every row.
Thanks!!!!!!
