Nested IIF issue

[AUGuy]

I'm sure there's something simple that I'm overlooking in this, but I'm getting Null values when the query runs.

IIf([Net Principal Balance]=0,"No",IIf([System]="CL",IIf([Import - Daily CL Raw]![Non-Accr-Flag]="Y","NonAccrual",IIf([Import - Daily CL Raw]![Non-Accr-Flag]="N","No",IIf([System]="MSP",IIf([Import - Daily MSP Raw]![Accrual Status]=2,"NonAccrual",IIf([Import - Daily MSP Raw]![Accrual Status]=3,"NonAccrual","No")))))))

If it doesnt meet any of the requirements it should show "No". However, when the System = MSP and the Accrual status is lets say 0 or 1, its showing null. Any thoughts?

Thanks!
Guy

 

[jzwp22]

Breaking down your expression as follows, I did not see any structural issues, but I did notice that you were using exclamation points rather than periods between table/query names and field names. Generally the exclamation marks are used for form references forms!formname... I don't know if that would make a difference here, but it would be a simple change.

IIf([Net Principal Balance]=0,"No",X)

X= IIf([System]="CL",Y)

Y= IIf([Import - Daily CL Raw].[Non-Accr-Flag]="Y","NonAccrual",Z)

Z= IIf([Import - Daily CL Raw].[Non-Accr-Flag]="N","No",A)

A= IIf([System]="MSP",B)

B= IIf([Import - Daily MSP Raw].[Accrual Status]=2,"NonAccrual",C)

C= IIf([Import - Daily MSP Raw].[Accrual Status]=3,"NonAccrual","No")

In terms of logic, are there more than two possible values for the [Non-Accr-Flag]? The way you have it, implies that there are.

 

[MSAccessRookie]

I'm sure there's something simple that I'm overlooking in this, but I'm getting Null values when the query runs.

IIf([Net Principal Balance]=0,"No",IIf([System]="CL",IIf([Import - Daily CL Raw]![Non-Accr-Flag]="Y","NonAccrual",IIf([Import - Daily CL Raw]![Non-Accr-Flag]="N","No",IIf([System]="MSP",IIf([Import - Daily MSP Raw]![Accrual Status]=2,"NonAccrual",IIf([Import - Daily MSP Raw]![Accrual Status]=3,"NonAccrual","No")))))))

If it doesnt meet any of the requirements it should show "No". However, when the System = MSP and the Accrual status is lets say 0 or 1, its showing null. Any thoughts?

Thanks!
Guy

The Standard form for an IIf() statement is something like the following:

IIf( {Case to Test}, {Value if Test=True}, {Value if Test=False} )
​

While the two Values can be actual values, they can also be the result of a function such as IIf(). I broke down the compound IIf() statement, and you can see that two of the IIf() statements ( IIf([System]="CL" and IIf([System]="MSP" ) have only two of the three parts, and it appears that the {Value if Test=True} part could be missing. Please see the example below to understand what I am referring to.

[COLOR=black][FONT=Verdana]IIf([Net Principal Balance]=0,"No",[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   [B]IIf([System]="CL",[/B][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       IIf([Import - Daily CL Raw]![Non-Accr-Flag]="Y", "NonAccrual",[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]           IIf([Import - Daily CL Raw]![Non-Accr-Flag]="N", "No[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]               [B]IIf([System]="MSP",[/B][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                   IIf([Import - Daily MSP Raw]![Accrual Status]=2, "NonAccrual",[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                       IIf([Import - Daily MSP Raw]![Accrual Status]=3, "NonAccrual", "No"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                       )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                   )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]               )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]           )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana])[/FONT][/COLOR]

 

[AUGuy]

I changed it to:
IIf([Net Principal Balance]=0,"No",IIf([System]="CL",IIf([Import - Daily CL Raw]![Non-Accr-Flag]="Y","NonAccrual",IIf([Import - Daily CL Raw]![Non-Accr-Flag]="N","No")),IIf([System]="MSP",IIf([Import - Daily MSP Raw]![Accrual Status]=2,"NonAccrual",IIf([Import - Daily MSP Raw]![Accrual Status]=3,"NonAccrual","No")))))

and it worked. I'm not 100% why it worked, but it worked nonetheless.


Thanks for the info both of you, it helped me in trying some fixes.

 

[jzwp22]

I stand corrected, thanks MSAccessRookie; I even listed the problem in my reply to the original poster, but failed to see it!

A= IIf([System]="MSP",B)

 

[MSAccessRookie]

I changed it to:
IIf([Net Principal Balance]=0,"No",IIf([System]="CL",IIf([Import - Daily CL Raw]![Non-Accr-Flag]="Y","NonAccrual",IIf([Import - Daily CL Raw]![Non-Accr-Flag]="N","No")),IIf([System]="MSP",IIf([Import - Daily MSP Raw]![Accrual Status]=2,"NonAccrual",IIf([Import - Daily MSP Raw]![Accrual Status]=3,"NonAccrual","No")))))

and it worked. I'm not 100% why it worked, but it worked nonetheless.


Thanks for the info both of you, it helped me in trying some fixes.

I believe that you should find that the reason that the fix worked is clear below. As you can see, in the new format, each IIf() has a valid value for all three parts. It should be noted that the result of correcting the IIf() format that was displayed in the previous post to have the missing values will be different from the result of the format below, and you might want to verify which format serves you best before you implement any solution.

[COLOR=black][FONT=Verdana]IIf([Net Principal Balance]=0,"No",[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   IIf([System]="CL",[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       IIf([Import - Daily CL Raw]![Non-Accr-Flag]="Y","NonAccrual",[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]           IIf([Import - Daily CL Raw]![Non-Accr-Flag]="N","No"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]           )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       ),[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       IIf([System]="MSP",[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]           IIf([Import - Daily MSP Raw]![Accrual Status]=2, "NonAccrual",[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]               IIf([Import - Daily MSP Raw]![Accrual Status]=3, "NonAccrual",[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                   "No"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]               )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]           )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana])[/FONT][/COLOR]

 

[MSAccessRookie]

I stand corrected, thanks MSAccessRookie; I even listed the problem in my reply to the original poster, but failed to see it!

A= IIf([System]="MSP",B)

No need to stand corrected. We each do our best to help a person with a need that we are able to understand. I am always glad to be part of a tream that helps an OP come to a solution.

 

[AUGuy]

thanks for the further explanation, it does make sense now. I will be chceking tomorrow to see which results both sets exclude, thanks for pointing out they were doing two different things

 

[Dairy Farmer]

but I did notice that you were using exclamation points rather than periods between table/query names and field names. Generally the exclamation marks are used for form references forms!formname

Read this article on Bang vs Dot