Nested IIF statement (AXP (2002) SP-1)

[rrdavis]

I am needing to export some data from my database. I am trying to construct it so that one of the fields pulls data from one of three difference fields, dependant upon the value of a fourth field. I figure I can do this with a compound and nested IIF statement. However, I cannot get it to work correctly. What is the limit for the depth of a nested IIF statement? The following one is 4 deep.

<font color=blue>IIf([RESP_TYPE]="C",[dboCLIN_SV_PCKI].[SEQUENCE],IIf([RESP_TYPE]="N",IIf([RESP_NUM]=0,"",[RESP_NUM]),IIF([RESP_TYPE]="M",[RESP_MEMO],"")))</font color=blue>

Translation: if TYPE evaluates to "C", capture FIELD1, otherwise test for TYPE = "N". If true, test whether FIELD2=0. If true, leave the field as a empty string. If false, capture FIELD2. If TYPE <> "N", then test if TYPE = "M". If true, capture FIELD3. If false, leave the field as an empty string. (I used FIELD1... in this translation just for simplicity. The statement above uses the actual field names).

In other words, there are three possible values for TYPE, corresponding the values in three different fields. Capture the value from a specific field dependant upon the value of TYPE.

I get an #ERROR code in records where TYPE evaluates to "M" or if it evaluates to "N" and the value is zero. If TYPE evaluates to "C" or "N" (and the value is greater that zero), I get the correct value.

Anyone have any ideas?

[HansV]

I don't see anything conspicuously wrong.
Does it help if you replace the two occurrences of "" in the expression by Null?

Note: if you have many IIf's, you can use the Switch function instead: Switch(condition_1,value_1,condition_2,value_2,... ). This evaluates the conditions, and assigns the value corresponding to the first condition that evaluates to True. In your case, it would be

Switch([RESP_TYPE]="C",[dboCLIN_SV_PCKI].[SEQUENCE],[RESP_TYPE]="N",IIf([RESP_NUM]=0,Null,[RESP_NUM]),[RESP_TYPE]="M",[RESP_MEMO])

It isn't any shorter, but slightly easier to read than many nested IIf's

[GARYPSWANSON]

First to answer your question, how deep can the nested if go. In 2000, I have gone 12 levels deep but the system gives me an error at 13 as I have just hit this limitation and had to re-program a database to fix it.

As to your error, I believe Hans has hit the nail on the head. Check for null values being passed in the data which is causing your If then statement to fail.

[rrdavis]

Thanks for the response, Hans. I was not aware of the Switch function. My query now works like a charm.