Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested Iif s (2003)

    Sorry to bother you with a syntax problem, but I can't get a nested Iif to behave - Trying to create a field which returns "GAP Detected" if there is the word "GAP" in a number of fields - there is probably a more elegant way to do this, if so feel free to correct me, but I was trying this:

    Check:Iif(([GAPAssetType] = "GAP", IIF( [GAPCostCode] = "GAP", IIf[GAPAcquision] = "GAP", Iif [GAPValuePer] = "GAP", Iif[GAPCost]= "GAP", Iif [GAPDepMethod]="GAP","GAP DETECTED","")

    No matter how I try and do this, it keeps returning "Parenthesis" requirements..... can anyone show me the correct way to frame it?

    Many thanks!

    Ina

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

    Re: Nested Iif s (2003)

    Do you want to return "GAP detected" if *all* of the fields contain the word "GAP", or if at least one of the fields contains the word "GAP"?
    And is the requirement that a field value equals "GAP", or that "GAP" is part of the field value (for example "I love GAP jeans")

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested Iif s (2003)

    I want to return "GAP DETECTED" if any of the fields have the word "GAP" (there will only be the word "GAP" or null in any of the fields).

    Cheers Hans

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

    Re: Nested Iif s (2003)

    Try this:

    Check: IIf(([GAPAssetType] = "GAP" Or [GAPCostCode] = "GAP" Or [GAPAcquision] = "GAP" Or [GAPValuePer] = "GAP" Or [GAPCost]= "GAP" Or [GAPDepMethod]="GAP","GAP DETECTED","")

    (The technical problem with your expression was that you omitted the opening parenthesis ( between IIf and the opening square bracket [ )

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested Iif s (2003)

    Hi Hans, no it still doesn't like it - tells me there is a syntax problem. Have got a fix by making a string of all the fields and searching for "GAP" within the created string. All the blooming brackets still defeat me! Thanks for your help tho'

    Ina

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

    Re: Nested Iif s (2003)

    Oops - I left a wrong opening parenthesis in there. It should be
    <code>
    Check: IIf([GAPAssetType]="GAP" Or [GAPCostCode]="GAP" Or [GAPAcquision]="GAP" Or [GAPValuePer]="GAP" Or [GAPCost]="GAP" Or [GAPDepMethod]="GAP","GAP DETECTED","")</code>

  7. #7
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested Iif s (2003)

    Yup, that works it! Thanks

Posting Permissions

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