Calculate years of service

[oxicottin]

Hello, I have to calculate years of service for each employee to show the correct vacation they would get. I am calculating the [YearsOfService] from the current year and changing the employees start date to year which works except the first vacation you get after your first 6 months and the way I have it now it shows that person as 0.

YearsOfService: ([Forms]![frm_Switchboard]![cboYear])-Year([EmpDateOfHire])


Can I calculate [YearsOfService] by months that way the employee who is greater than 6 months can get the 1? If so how?

1 week Vac after six months of service
2 week Vac after (2) [YearsOfService]
3 week Vac after eight (8) [YearsOfService]
4 week Vac after fifteen (15) [YearsOfService]
5 week Vac after twenty-five (25) [YearsOfService]

SELECT tbluEmployees.EmpFName, ([Forms]![frm_Switchboard]![cboYear])-Year([EmpDateOfHire]) AS YearsOfService, IIf([YearsOfService]<=0,0,IIf(DateAdd("m",6,[YearsOfService])<=1,1,IIf([YearsOfService]<=7,2,IIf([YearsOfService]<=14,3,IIf([YearsOfService]<=24,4,IIf([YearsOfService]>=25,5)))))) AS acWeeksAquired
FROM tbluEmployees;

 

[theDBguy]

I wonder if using DateDiff() would help in this situation.

 

[plog]

1. It seems odd that this calculation is based on an input value on a form. Why aren't you doing it using today's date (Date())?

2. You've got a database at your disposal, why hardcode a bunch of vacation allocation values into a query? Put that into a table, then JOIN to get WeeksAcquired.

3. Use DateDiff(https://www.techonthenet.com/access/functions/date/datediff.php) to determine the difference in months between two dates.

 

[oxicottin]

Ok thanks guys.... I had to do date diff by counting days to be more accurate. It works thank you!

SELECT tbluEmployees.EmpFName, DateDiff("d",[EmpDateOfHire],Date()) AS DaysOfService, IIf([DaysOfService]<=181,0,IIf([DaysOfService]<=365,1,IIf([DaysOfService]<=2555,2,IIf([DaysOfService]<=5110,3,IIf([DaysOfService]<=8760,4,IIf([DaysOfService]>=9125,5)))))) AS VacWeeksAquired
FROM tbluEmployees;

 

[theDBguy]

Ok thanks guys.... I had to do date diff by counting days to be more accurate. It works thank you!

Glad to hear you got it sorted out. Good luck with your project.