Multiple nested iif statement in Access

Question

I am trying to calculate the number of vacation week an employee is entitled to using their years of service, with these categories:

0-4 years = 2 weeks
5-14 years = 3 weeks
15-24 years = 4 weeks
25 and up = 5 weeks

I found some examples and tried doing a nested iif statement but keep getting an error message. Can someone tell me what I am doing wrong?

VacationWeeks: IIf([YearsInService]<=4, "2",
               IIf([YearsInService]is between 5 and 14, "3",
               IIf([YearsInService]is between 15 and 24, "4",
               IIf([YearsInService]>=25, "5", ”0”))))

Answer 1

You are likely receiving error messages because of two issues with your code:

1. A BETWEEN statement has the syntax:

   expr [Not] Between value1 And value2
   

   You should not include the word is:

   IIf([YearsInService] is between 5 and 14,
                        ^-------------------- this shouldn't be here
   

2. Your final else argument encloses the string in 'smart quotes' or Unicode character 0x201C:

   IIf
   (
       [YearsInService] >= 25,
       "5",
       ”0” <--- here
   )
   

   As opposed to the standard double-quote, which is Unicode/ASCII character 0x0022:

   IIf
   (
       [YearsInService]>=25,
       "5",
       "0"
   )
   

Correcting these two issues would yield an IIF statement such as:

IIf
(
    [YearsInService] <= 4,
    "2",
    IIf
    (
        [YearsInService] between 5 and 14,
        "3",
        IIf
        (
            [YearsInService] between 15 and 24,
            "4",
            IIf
            (
                [YearsInService] >= 25,
                "5",
                "0"
            )
        )
    )
)

However, you may find the use of a SWITCH statement more readable:

Switch
(
    [YearsInService] <=  4, "2",
    [YearsInService] <= 14, "3",
    [YearsInService] <= 24, "4",
    [YearsInService] >  24, "5",
    True, "0"
)

The last test expression provides a catch-all default to account for nulls and still return a string value - I'll let you decide whether you wish to include or omit this, depending on the requirements of your application.