Multiple IIf and AND in a query

[mdmahfuzcse]

Hi, I am working in a factory as a HR officer. Recently I have made a database on Employee Salary. There is a query of yearly bonus based on Employee’s Job duration. The query is:
Bonus: IIf([JobDuration]>=12,[Main_Salary]*0.4,IIf([JobDuration]>=7,[Main_Salary]*0.3,IIf([JobDuration]>=4,[Main_Salary]*0.2,IIf([JobDuration]>=2,[Main_Salary]*0.1,[Main_Salary]*0))))
And it works nicely. The problem is if I want to add more condition such as JobDuration>=12 AND Department=Admin then it doesn’t run. My Required Condition is:
If ([JobDuration]>=12 and [Department] =”Admin” then [Main_Salary]*1.0 Otherwise [Main_Salary]*0.6
If ([JobDuration]>=7 and [Department] =”Admin” then [Main_Salary]*0.8 Otherwise [Main_Salary]*0.3
If ([JobDuration]>=4 and [Department] =”Admin” then [Main_Salary]*0.6 Otherwise [Main_Salary]*0.2
If ([JobDuration]>=2 and [Department] =”Admin” then [Main_Salary]*0.4 Otherwise [Main_Salary]*0.1
Otherwise [Main_Salary]*0
In this situation what is the solution? What should be the query on the mentioned condition?
It would be highly helpful for me if someone help me.
Thanks in advanced.

 

[Joe4]

Welcome to the Board!

You can only nest up to 7 levels in an IIF statement. Instead of trying to build all of this into a single, long, convoluted formula, I would recommend creating a User Defined Function (UDF) instead. Just feed the formula the two variables, and have it do the calculations and return the result you need.

Here are some link that show you how to do that:
Creating and Using User Defined Functions in Access » Bacon Bits
Creating Access UDFs that Accept Arguments » Bacon Bits
MS Access: Creating a custom round function