Expression with three criteria/variables

[Guest]

I am trying to create an expression to calculate annual leave entitlements
for staff, based on the following premises.

1) If the staff member has completed less than (but not equal to) five years
service, the annual leave entitlement (a) will be
a = mh
Where m is the entitlement for less than five years service and h is the
number of hours worked.

2)If the staff member has completed more than (or equal to) 5 and less than
(but not equal to) 10 years service, the annual leave entitlement (b) will be:
b = nh
Where n is the entitlement for between 5 and 10 years service and h is the
number of hours worked.

3)If the staff member has completed more than 10 years service, the annual
leave entitlement (c) will be:
c = ph
Where p is the entitlement for more than 10 years service and h is the
number of hours worked.

I have a field labelled “Start Dateâ€, which I was hoping to use to calculate
the number of years service. The main problem I am having is finding a
function that will cope with the three variables/criteria as detailed above.
(If there were only two variables/criteria, I was thinking of using the IIf
function.)

Does anyone have any ideas that do not involve VBA?

Many thanks

Richard

 

[kingston via AccessMonster.com]

You're on the right track with the IIF statement:

IIF(Date()-[StartDate]<365*5,mh,IIF(Date()-[StartDate]<365*10,nh,ph))

This isn't right because 5 years isn't equal to 365*5, but you didn't have a
problem with that. I just put that there as a quick answer to your question
about how to evaluate multiple criteria.

 

[Guest]

Create a lookup table and use the query below.
Lookup_Leave ---
Low High Leave
0 5 2
5 10 4
10 99 6

SELECT EM_List.PERSONNEL_NO, EM_List.HIRE_DATE,
DateDiff("yyyy",[HIRE_DATE],Date()) AS Expr1, Lookup_Leave.Leave
FROM EM_List, Lookup_Leave
WHERE (((DateDiff("yyyy",[HIRE_DATE],Date()))>=[Low] And
(DateDiff("yyyy",[HIRE_DATE],Date()))<[High]));

 

[Douglas J. Steele]

You can nest IIf statements:

IIf(Service < 5, m*h, IIf(Server < 10, n*h, p*h))