Iif/IsNull Question

  • Thread starter Thread starter Erin
  • Start date Start date
E

Erin

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!!!!!!
 
Yep, '&' is not a logical function.

Expr1: IIf([LatestOccurenceWarning] Is Null AND [LatestTardyWarning] Is Null
AND [LatestMissedPunchesWarning] Is Null AND [LatestNoCallNoShowWarning],
"NO","YES")
 
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!!

KARL DEWEY said:
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.


Erin said:
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!!!!!!
Click to expand...
Click to expand...
 
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.


Erin said:
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!!

KARL DEWEY said:
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.


Erin said:
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!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
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.


Erin said:
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!!

KARL DEWEY said:
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!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
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.


Erin said:
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!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
PERFECT, that is EXACTLY what I needed!!! Thank you!!!!!!!

John Spencer said:
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!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top