# Calculate years of service (1 Viewer)

#### oxicottin

##### Learning by pecking away....
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’m here to help
Staff member
I wonder if using DateDiff() would help in this situation.

#### plog

##### Banishment Pending
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

##### Learning by pecking away....
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

##### I’m here to help
Staff member
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.

Replies
1
Views
437
Replies
3
Views
678
Replies
4
Views
1,000
Replies
4
Views
916
Replies
2
Views
1,042