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?