Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

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

    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.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •