Query Question- IIF Statement with Multiple Conditions

[TBcontroller]

Hi,



So, I have only been using access for a week and am looking for some input on expressions. Specifically IIF statements with multiple conditions. I cannot seem to get it to work properly.

Maybe someone has input that I am missing.

Expression:
2017 YE Forecast: IIf([NAFTA Cost Base] Is Not Null,[NAFTA Cost Base],IIf([2016 Certification] Is Not Null And [Cert_Status]=" ",[Cost Per Vehicle]-[Traced Val],Null))

What I am trying to achieve:
I would like to have two conditions.
1. IIf([NAFTA Cost Base] Is Not Null,[NAFTA Cost Base],Null) (this part is working)
OR
2. IIf([2016 Certification] Is Not Null And [Cert_Status]=" ",[Cost Per Vehicle]-[Traced Val],Null) (this part is not)

Any help would be GREATLY appreciated.

[JoeM]

The way that nested IIF statements work, is once it finds a True condition, it stops right there and does not look at anything else after that.

So, in your example, it is impossible for the second IIF to ever result in TRUE, because if [2016 Certification] is Not Null, it will end there on the first check and never get to the second check.
Switch your two IIF statements around and it should work.

[Bulzie]

Seems like it would work unless I am missing it, I put it in vba below. If [NAFTA Cost Base] is null, should go to 2nd part and check those 2 fields. Maybe it is going there but either [2016 Certification] is null or [Cert_Status] is not blank to allow it to do that calculation? Check your data to make sure you have a record where [NAFTA Cost Base] is null and [2016 Certification] is not null AND [Cert_Status]=" " for it to get to that last calculation part.

2017 YE Forecast: IIf([NAFTA Cost Base] Is Not Null,[NAFTA Cost Base],IIf([2016 Certification] Is Not Null And [Cert_Status]=" ",[Cost Per Vehicle]-[Traced Val],Null))

If Not Isnull ([NAFTA Cost Base]) Then

Me.2017 YE Forecast = [NAFTA Cost Base]

Else

If Not Isnull([2016 Certification]) And [Cert_Status]=" " Then

Me.2017 YE Forecast = [Cost Per Vehicle]-[Traced Val]

Else

Me.2017 YE Forecast = null

End If

End If

[JoeM]

My mistake, I thought it was checking the same field for Null in both IIF statements.

Ugh, time to get some more caffeine!

[TBcontroller]

Thanks for the input. I have double checked and my data matches my inputs. Except for when [Traced Val] is blank and not "0".

I had to add another condition.

2017 YE Forecast: IIf([NAFTA Cost Base] Is Not Null,[NAFTA Cost Base],IIf([2016 Certification] Is Not Null And [NE Status]="" And [Traced Val] Is Not Null,[Cost Per Vehicle]-[Traced Val],IIf([2016 Certification] Is Not Null And [NE Status]="" And [Traced Val] Is Null,[Cost Per Vehicle],Null)))

Just happy its Friday.

[Bulzie]

So does it work now? If not break it up and run those individual IIF statements in their own column to see if they return the data you expect. That might narrow down where the issue is. If you do not get the expected results, could be data or the code.

[TBcontroller]

Yes it does work now.

[Bulzie]

Awesome!!!

[ArviLaanemets]

Somewhat simpler version:

IIf([NAFTA Cost Base] Is Not Null,[NAFTA Cost Base],IIf([2016 Certification] Is Not Null And [NE Status]="",[Cost Per Vehicle]-Nz([Traced Val],0),Null))