Results 1 to 3 of 3

Thread: Nested IIF

  1. #1
    Join Date
    May 2017
    Posts
    2

    Answered: Nested IIF

    I am trying to write a nested IIF or switch statement and I keep getting the error "The expression you entered has a function containing the wrong number of arguments. It does not matter if I do a switch or nested IIF if I do more than one IIF I get the error. If I take the middle two IIF's out of this function with no other changes it will work fine. I cannot seem to find out what is wrong with my query as I have never had these kind of issues with switch or nested IIF statements. I could do it as a table but there are a lot of other combinations I would need to include to link it as a table and trying to avoid that as I already have about 12 joins in this SQL statement and it takes a long time to run.

    Company:
    IIf(([DEA]![Company]="2300" And [DEA]![Department]="2309"),"2309 - AudaExplore(US Headquarters)",
    IIf([DEA]![Company]="2300" And [DEA]![Department]="2303"),"2303 - USA PDC",
    IIf([DEA]![Company]="2300" And [DEA]![Department]="2321"),"2321 - VIS (Autosource)",
    ([Legal Entities]![Legal Entity] & " - " & [Legal Entities]![Legal Entity Name]))

  2. Best Answer
    Posted by scoobydoo0416

    "Try this

    IIf([DEA]![Company]="2300" And [DEA]![Department]="2309","2309 - AudaExplore(US Headquarters)",
    IIf([DEA]![Company]="2300" And [DEA]![Department]="2303","2303 - USA PDC",
    IIf([DEA]![Company]="2300" And [DEA]![Department]="2321","2321 - VIS (Autosource)",
    [Legal Entities]![Legal Entity] & " - " & [Legal Entities]![Legal Entity Name])))"


  3. #2
    Join Date
    May 2017
    Posts
    1
    Provided Answers: 1

    NestedIIF

    Try this

    IIf([DEA]![Company]="2300" And [DEA]![Department]="2309","2309 - AudaExplore(US Headquarters)",
    IIf([DEA]![Company]="2300" And [DEA]![Department]="2303","2303 - USA PDC",
    IIf([DEA]![Company]="2300" And [DEA]![Department]="2321","2321 - VIS (Autosource)",
    [Legal Entities]![Legal Entity] & " - " & [Legal Entities]![Legal Entity Name])))

  4. #3
    Join Date
    May 2017
    Posts
    2

    Thanks

    Thanks that worked.

Posting Permissions

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