Calculate years of service (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 16:18
Joined
Jun 26, 2007
Messages
850
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;

Capture.JPG
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:18
Joined
Oct 29, 2018
Messages
21,296
I wonder if using DateDiff() would help in this situation.
 

plog

Banishment Pending
Local time
Today, 15:18
Joined
May 11, 2011
Messages
11,586
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....
Local time
Today, 16:18
Joined
Jun 26, 2007
Messages
850
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
Local time
Today, 13:18
Joined
Oct 29, 2018
Messages
21,296
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.
 

Users who are viewing this thread

Top Bottom