Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Change from IIF to Switch

    I want to add several additional IIf statements to the expression below and have been advised there is a limit of 7 consecutive statements for IIf so better to use Switch function to do the same thing. Have tried using Switch but keep getting errors.

    Here is original IIF which works fine

    Date Due: IIf([Course I]="Epilepsy Awareness & Rescue Medication",DateAdd('m',12,[Date Completed]),IIf([Course I]="Epilepsy Awareness & Rescue Med Refesher",DateAdd('m',14,[Date Completed]),IIf([Course I]="First Aid First Aid",DateAdd('m',14,[Date Completed]))))


    Here is my attempt at Switch version

    Date Due: Switch([Course I]="Epilepsy Awareness & Rescue Medication",DateAdd('m',12,[Date Completed]),IIf([Course I]="Epilepsy Awareness & Rescue Med Refesher",DateAdd('m',14,[Date Completed]),IIf([Course I]="First Aid First Aid",DateAdd('m',14,[Date Completed]))))


    Which returns #Error in the Date Due field


    Could anyone please point me in the right direction. It would help me if apart from pointing out where I've gone wrong someone could add an additional DateAdd statement to the Switch expression, as apart from it still not working I was having initial trouble with the number of ending parentheses.

    Thanks
    Keith

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You would drop all the IIf() functions, not just the first. From help, an example of a Switch() function:

    Switch(CityName = "London", "English", CityName = "Rome", "Italian", CityName = "Paris", "French")

    You would replace instances like

    CityName = "London"

    with your tests:

    [Course I]="Epilepsy Awareness & Rescue Medication"

    and corresponding values like

    "English"

    with the corresponding

    DateAdd('m',12,[Date Completed])
    Paul

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    Quote Originally Posted by pbaldy View Post
    You would drop all the IIf() functions, not just the first. From help, an example of a Switch() function:

    Switch(CityName = "London", "English", CityName = "Rome", "Italian", CityName = "Paris", "French")

    You would replace instances like

    CityName = "London"

    with your tests:

    [Course I]="Epilepsy Awareness & Rescue Medication"

    and corresponding values like

    "English"

    with the corresponding

    DateAdd('m',12,[Date Completed])

    God sorry,getting tired and bleary eyed, done that

    Date Due: Switch([Course I]="Epilepsy Awareness & Rescue Medication",DateAdd('m',12,[Date Completed]),([Course I]="Epilepsy Awareness & Rescue Med Refesher",DateAdd('m',14,[Date Completed]),([Course I]="First Aid First Aid",DateAdd('m',14,[Date Completed]))))

    Now getting a syntax 'Comma' error, can't see a missing or extraneous comma .........

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Extraneous parentheses in red:

    Date Due: Switch([Course I]="Epilepsy Awareness & Rescue Medication",DateAdd('m',12,[Date Completed]),([Course I]="Epilepsy Awareness & Rescue Med Refesher",DateAdd('m',14,[Date Completed]),([Course I]="First Aid First Aid",DateAdd('m',14,[Date Completed]))))
    Paul

  5. #5
    Join Date
    Jul 2009
    Posts
    39

    Thanks

    Once again Paul thanks, I'll be seeing parentheses in my dreams tonight !

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo, happy to help. Some people count sheep, for you it's parentheses!
    Paul

Posting Permissions

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