How to do Multiple IIF / CASE query

[caz]

Hello

I was wondering if anyone knows how to do the following;

I have the number of years staff have been working for the company which is extracted from the startdate.

Based on the number of years service employees are entitiled to extra days leave, so I need to do the following:

IIF([YEAR]<5, 24) and IIF([YEAR] between 5 and 9, 25) and IIF([YEAR] between 10 and 14, 26) and IIF([YEAR] between 15 and 19, 27) and IIF([YEAR] between 20 and 24, 28) and IIF([YEAR] > 25 , 29)

The above syntax does not work...

HOw do I achieve the above? Any suggestion would be gratefully recieved.
Ta

 

[raskew]

Caz -

'Year' is a reserved word in Access and invites problems when used as a field name. With the formula you've provided, an Iif() statement combined with the Choose() function should give you what you're after. Example:

numyears = 14
x = Iif(numyears >= 25, 29, choose(int(numyears/5) + 1, 24, 25, 26, 27, 28))
? x
 26

HTH - Bob

 

[caz]

Multiple Case / Iif

Hello

Thanks for the reply.

YEar is just an alias for a date calculation. I have a Start Date - Format(NOW(),yyyy) aliased YEAR I have changed it to SERVICE.

So SERVICE is a number of years that the employee has been working for the company.

I need to apply the following formula:
SERVICE less than 5 then 24 days
SERVICE 5 to 9 years then 25
SERVICE 10-14 then 26
SERVICE 15 -19 then 27
SERVICE 20 -24 then 28
SERVICE 25 + 29

Holiday allowance needs to be in a column in the query for each row to show the holiday entitlement.

How do I code this in the query grid?

 

[raskew]

Add a calculated field, e.g.

HolidayAllow: Iif(service>= 25, 29, choose(int(service/5) + 1, 24, 25, 26, 27, 28))

Bob