Nesting IIf statements

[Guest]

I have a calculated field in a query. I need it to return the contents of field2, if field1 is null, return the contents of field3, if field1 and field2 both are null, return the contents of field4, if field1,and field2, and field3 are null, otherwise return the contents of field1. I have tried the following with no luck:
EXP: IIf([field1] is null, [field2],IIf [field1] and [field2] is null, [field3], IIf [field1] and [field2] and [field3] is null,[field4],[field1])))

I get the correct results with EXP: IIf([field1] is null, [field2],[field1]) But I can't bring it any further. Any help will be appreciated.

 

[Gerald Stanley]

Try something along the lines of
EXP: IIf(IsNull([field1]), [field2],IIf (IsNull([field1])
and IsNull([field2]), [field3], IIf (IsNull([field1]) and
IsNull([field2]) and IsNull([field3]),[field4],[field1])))

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I have a calculated field in a query. I need it to return

the contents of field2, if field1 is null, return the
contents of field3, if field1 and field2 both are null,
return the contents of field4, if field1,and field2, and
field3 are null, otherwise return the contents of field1. I
have tried the following with no luck:

EXP: IIf([field1] is null, [field2],IIf [field1] and

[field2] is null, [field3], IIf [field1] and [field2] and
[field3] is null,[field4],[field1])))

I get the correct results with EXP: IIf([field1] is null,

[field2],[field1]) But I can't bring it any further. Any
help will be appreciated.

 

[Tony Vrolyk]

If field1 and field2 are both null, your statement will never get past the
first IIF because field1 is null and it will therefore return field2. Your
example (and mine) does not use correct syntax. I presume you did this for
clarity and are not acutally as your criteria. You would of course
have to use the syntax iif(isnull([field1]) and
isnull([field2]),[field3],...

Maybe start with you most restricted criteria first and work your way up to
the most lenient. Using your example here is what I suggest

EXP: IIf([field] and [field2] and [field3] is null,[field4],IIf [field1] and
[field2] is null,[field3],[field1] is null,[field2],"Nothing is null!)

Tony

I have a calculated field in a query. I need it to return the contents of

field2, if field1 is null, return the contents of field3, if field1 and
field2 both are null, return the contents of field4, if field1,and field2,
and field3 are null, otherwise return the contents of field1. I have tried
the following with no luck:

EXP: IIf([field1] is null, [field2],IIf [field1] and [field2] is null,

[field3], IIf [field1] and [field2] and [field3] is
null,[field4],[field1])))

I get the correct results with EXP: IIf([field1] is null,

[field2],[field1]) But I can't bring it any further. Any help will be
appreciated.

 

[John Spencer (MVP)]

Try nesting NZ statements instead. Or write a custom function. Nesting IIF's
this deep will almost always give you a headache.

Nz(Field1,Nz(Field2,Nz(Field3,Field4)))

 

[Guest]

Ron,

If you want to use nested IIF() statements, one of these should work based on your conditions

using the IsNull() function

=IIf(IsNull([field1]) And IsNull([field2]) And IsNull([field3]),[field4],IIf(IsNull([field1]) And IsNull([field2]),[field3],IIf(IsNull([field1]),[field2],[Field1]))

using the IS Operator

=IIf([field1] Is Null And [field2] Is Null And [field3] Is Null,[field4],IIf([field1] Is Null And [field2] Is Null,[field3],IIf([field1] Is Null,[field2],[Field1]))

Stev

 

[Guest]

Ron

In your case, you have to start checking first with the most conditions, then the second most conditions, down to the least conditions. When I replied to you, I had checked the formulas using an unbound form with unbound text boxes. So this time I created a MDB, and a table (teble1) with five fields: ID, field1, field2, field3, field4

I added records as follows: the first record I entered text in field4. The next record I added text to field4 and field3; the third record has text entered into field2, field3, and field4; the fourth record has text in all fields

I created a query and added all fields from table1 to the grid; in the sixth column I pasted the following

result: IIf(IsNull([field1]) And IsNull([field2]) And IsNull([field3]),[field4],IIf(IsNull([field1]) And IsNull([field2]),[field3],IIf(IsNull([field1]),[field2],[Field1]))

the SQL of the query is

SELECT [Table1].[id], [Table1].[field1], [Table1].[field2], [Table1].[field3], [Table1].[field4], IIf(IsNull([field1]) And IsNull([field2]) And IsNull([field3]),[field4],IIf(IsNull([field1]) And IsNull([field2]),[field3],IIf(IsNull([field1]),[field2],[Field1]))) AS result, IIf([field1] Is Null And [field2] Is Null And [field3] Is Null,[field4],IIf([field1] Is Null And [field2] Is Null,[field3],IIf([field1] Is Null,[field2],[Field1]))) AS result
FROM Table1

The results were

field1, 2 & 3 null result = field
field1 & 2 null result = field
field 1 null result = field
no fields null result = field

Of course, you need to use your field names

Could you post the SQL of your query

Stev

 

[Guest]

Thank you Steve

I will try again, and let you know how I make out. If I don't succeed this time, I will post my SQL and maybe that will help you troubleshoot for me. I really appreciate your patience and help

Ro

----- SteveS wrote: ----

Ron

In your case, you have to start checking first with the most conditions, then the second most conditions, down to the least conditions. When I replied to you, I had checked the formulas using an unbound form with unbound text boxes. So this time I created a MDB, and a table (teble1) with five fields: ID, field1, field2, field3, field4

I added records as follows: the first record I entered text in field4. The next record I added text to field4 and field3; the third record has text entered into field2, field3, and field4; the fourth record has text in all fields

I created a query and added all fields from table1 to the grid; in the sixth column I pasted the following

result: IIf(IsNull([field1]) And IsNull([field2]) And IsNull([field3]),[field4],IIf(IsNull([field1]) And IsNull([field2]),[field3],IIf(IsNull([field1]),[field2],[Field1]))


the SQL of the query is

SELECT [Table1].[id], [Table1].[field1], [Table1].[field2], [Table1].[field3], [Table1].[field4], IIf(IsNull([field1]) And IsNull([field2]) And IsNull([field3]),[field4],IIf(IsNull([field1]) And IsNull([field2]),[field3],IIf(IsNull([field1]),[field2],[Field1]))) AS result, IIf([field1] Is Null And [field2] Is Null And [field3] Is Null,[field4],IIf([field1] Is Null And [field2] Is Null,[field3],IIf([field1] Is Null,[field2],[Field1]))) AS result
FROM Table1


The results were

field1, 2 & 3 null result = field
field1 & 2 null result = field
field 1 null result = field
no fields null result = field

Of course, you need to use your field names

Could you post the SQL of your query


Stev

 

[SteveS]

I have tried it again. However I determined that the fields actually have zero length strings instead of being null. It still did not work. I have included my SQL statement to see if you can find my problem. My query does not move socket option one to the new field if socket option one does not ="", and it does not consistantly move socket option two to the new field when socket option one ="". Any further help will be appreciated, this is driving me crazy.

SELECT Socketoption_regnumbers.[Socket Option One], Socketoption_regnumbers.[Socket Option Two], Socketoption_regnumbers.[Socket Option Three], Socketoption_regnumbers.[Design Part Number], IIf([Socketoption_regnumbers]![Socket Option One] And [Socketoption_regnumbers]![Socket Option Two] And [Socketoption_regnumbers]![Socket Option Three]="",[Socketoption_regnumbers]![Design Part Number],IIf([Socketoption_regnumbers]![Socket Option One] And [Socketoption_regnumbers]![Socket Option Two]="",[Socketoption_regnumbers]![Socket Option Three],IIf([Socketoption_regnumbers]![Socket Option One]="",[Socketoption_regnumbers]![Socket Option Two],[Socketoption_regnumbers]![Socket Option One]))) AS Expr1
FROM Socketoption_regnumbers;

Ron,

You are close... The problem is how you are writing the IIF condition.

When you write:

IIF( field1 AND
field2 AND
field3 = ""

you want and understand that each field should be tested for an empty string,
but Access is read as :

IIF( field1 = TRUE AND
field2 = TRUE AND
field3 = "",

which is not what you want. So it really MUST be explicitly written to test each
field:

IIF( field1 = "" AND
field2 = "" AND
field3 = "" ,



Try this for your SQL:

SELECT Socketoption_regnumbers.[Socket Option One],
Socketoption_regnumbers.[Socket Option Two], Socketoption_regnumbers.[Socket
Option Three], Socketoption_regnumbers.[Design Part Number],
IIf([Socketoption_regnumbers]![Socket Option One] ="" And
[Socketoption_regnumbers]![Socket Option Two] ="" And
[Socketoption_regnumbers]![Socket Option
Three]="",[Socketoption_regnumbers]![Design Part
Number],IIf([Socketoption_regnumbers]![Socket Option One] ="" And
[Socketoption_regnumbers]![Socket Option
Two]="",[Socketoption_regnumbers]![Socket Option
Three],IIf([Socketoption_regnumbers]![Socket Option
One]="",[Socketoption_regnumbers]![Socket Option
Two],[Socketoption_regnumbers]![Socket Option One]))) AS Expr1
FROM Socketoption_regnumbers;



I added the test for the empty strings.


It should work now... let me know

 

[Guest]

Bingo!!!!

THANK YOU STEVE, FOR ALL YOUR HELP AND PATIENCE. I certainly won't forget this problem

Ron