I have the following set up in a qu

[Carol57]

I have the following set up in a query to determine the Years of Service of Employees:

YearsService: DateDiff("y",[YearHired],[Year])

[Year] contains the years 2000 through to 2005. This query will automate the vacation allowance of each employee.

Everything calculates correctly, except if an employee's YearHired date is before our starting Year field which results in a -YearsService number. See Below

ID Name YearHired Year YearsService
A H. Whitehouse 2002 2002 0
A H. Whitehouse 2002 2004 2
A H. Whitehouse 2002 2003 1
A H. Whitehouse 2002 2000 -2
A H. Whitehouse 2002 2001 -1
A H. Whitehouse 2002 2005 3

This query is then used as the sub for an additional query to determine the actual vacation weeks with the following:

VacWeeks: IIf([YearsService]>&quot;0&quot; And ([YearsService])<=&quot;10&quot;,&quot;2 Weeks&quot;,IIf([YearsService]>=&quot;10&quot; And ([YearsService])<=&quot;15&quot;,&quot;3 Weeks&quot;,IIf([YearsService]>&quot;15&quot;,&quot;4 Weeks&quot;,IIf([YearsService]<&quot;1&quot;,&quot;0 Weeks&quot)))

Right now if anyone has a negative years of service for the base year, it is maxing out the vacation allotment of 4 weeks even though they are not entitled to any.

Could someone shed some light?

Thanks.

 

[coco86]

It's because [YearsOfService] is a string and not a number. Therefore, &quot;-1&quot; > &quot;15&quot;

Try this:

YearsService: val(DateDiff(&quot;y&quot;,[YearHired],[Year]))

And this:

VacWeeks: IIf([YearsService]>0 And ([YearsService])<=10,&quot;2 Weeks&quot;,IIf([YearsService]>=10 And ([YearsService])<=15,&quot;3 Weeks&quot;,IIf([YearsService]>15,&quot;4 Weeks&quot;,IIf([YearsService]<1,&quot;0 Weeks&quot)))

 

[Carol57]

Thank you Coco86 - Works great now!!!!