Using iif for Multiple Conditions

[kwilbur]

Hello All,



I have been trying to figure out why my iif function is only returning the first two options. 1 and 2. I need it to return 5 options. All having ranges. My expression is as follow:

iff(DaysEmployed<90,"90",DaysEmployed>=90<730,"1", DaysEmployed>=730<1460,"2",
DaysEmployed>=1460<4745,"3",DaysEmployed>=4745<912 4,"4",DaysEmployed>=9124,"5")

Maybe Im using the wrong function so I used Switch instead and it still was only reading the first two criteria. I even removed the equals sign but still the same. What am I doing wrong and how could I fix it. I need it to pick from a range and return a number. Is there a way to do this?

Thanks in advance

[pbaldy]

I'd use Switch, but in either case your range test is wrong. It would have to be

DaysEmployed>=90 And DaysEmployed <730

That said, it's much simpler. You rely on the fact that Access will stop testing after the first successful test, so simply test for <90, <730, <1460, etc.

[kwilbur]

After adding the Ands in now Im getting the error for "the expression you entered has a function containing the wrong number of arguments"

Plan Rate: iif([DaysEmployed]<90,"90",[DaysEmployed]>=90 And [DaysEmployed],730,"1",[DaysEmployed]>=730 And [DaysEmployed]<1460,"2",[DaysEmployed]>=1460 And [DaysEmployed]<4745,"3",[DaysEmployed]>=4745 And [DaysEmployed]<9124,"4",[DaysEmployed]>=9124,"5")

Even did the switch you suggested and had the same error. Obviously didn't have the same formula. But written out the usual way with and.

[kwilbur]

ignore the comma in front of 730. i didn't type it in write.

[kwilbur]

scratch that. I found it. Thanks pbaldy without the ands I wouldn't have figured it out. I used your suggestion of using switch. much cleaner and simpler.

cheers

[pbaldy]

No problem. Apparently you didn't like the simplicity of just testing for "less than" progressively larger numbers? For example, you don't have to test for >=90 in the second test. If it was <90, it will never get to the second test.