Building a conditional expression in Access

[Guest]

I am trying to build a conditional expression in an Access query that will
tell me how many weeks of vacation an employee has based on years of service
(which is computed using a different query).

For example 1 yr of service = 1 week; 2 to >5 yrs of service = 2 weeks; 5
to >10 = 3 weeks; 10 to >20 = 4 weeks; 20 to >30 = 5 weeks; <30 = 6 weeks.

Please advise how I can do this. Thanks

 

[Guest]

I assume you have a field that return the number of years, if that the case try

IIf([FieldName] = 1, 1 , IIf([FieldName] Between 2 And 5 , 2 ,
IIf([FieldName] Between 5 And 10 ,3 , IIf([FieldName] Between 10 And 20 ,4
,IIf([FieldName] Between 20 And 30, 5 ,6)))))

 

[Guest]

Thanks

I assume you have a field that return the number of years, if that the case try

IIf([FieldName] = 1, 1 , IIf([FieldName] Between 2 And 5 , 2 ,
IIf([FieldName] Between 5 And 10 ,3 , IIf([FieldName] Between 10 And 20 ,4
,IIf([FieldName] Between 20 And 30, 5 ,6)))))

--
Good Luck
BS"D

I am trying to build a conditional expression in an Access query that will
tell me how many weeks of vacation an employee has based on years of service
(which is computed using a different query).

For example 1 yr of service = 1 week; 2 to >5 yrs of service = 2 weeks; 5
to >10 = 3 weeks; 10 to >20 = 4 weeks; 20 to >30 = 5 weeks; <30 = 6 weeks.

Please advise how I can do this. Thanks