I need to create a function in my access DB that can calculate vacation accruals. I found this code on here but it's for accruing vacation time based on years of service. Our accrual policy is accruing by months, not years. Can anyone possibly help me re-write this code or help me create my own function to accrue vacation time? Here's what our policy states:

< 3 Months 0.00
>=3 Months & < 5yrs 6.67 hours/mo
>=5 Years & < 10yrs 8.00 hours/mo
>= 10 Years & < 15yrs 10.00 hours/mo
>= 15 Years & < 18 yrs 11.33 hours/mo
>= 18 Years & < 20 yrs 12.00 hours/mo
> 20 Years & < 40 yrs 13.33 hours/mo
>= 40 Years 14.00 hours/mo

'This function will use the passed in Employee Start Date to calculate 'the total time they have accrued off. This function will return the 'number of hours accrued or -1 if an error occurs. 'Turn on error handling On Error GoTo Error_Handler 'Declare variables Dim lngYearsWorked As Long 'Get Years worked lngYearsWorked = DateDiff("yyyy", inStartDate, Date) 'Check the Accrual Type If inAccrualType = "PER" Then 'Check number of years worked If lngYearsWorked <= 6 Then 'Return 80.08 per year fcnCalculateAccruedTimeOff = lngYearsWorked * 80.08 ElseIf lngYearsWorked >= 7 And lngYearsWorked <= 9 Then 'Return 80.08 per year for the first 6 (480.48), then 87.88 for 'each year from 7 - 9 fcnCalculateAccruedTimeOff = 480.48 + ((lngYearsWorked - 6) * 87.88) ElseIf lngYearsWorked >= 10 Then 'Return 80.08 per year for the first 6 (480.48), 87.88 for years '7 - 9 (263.64), and 96.2 per year for each year 10 and after. fcnCalculateAccruedTimeOff = 480.48 + 263.64 + ((lngYearsWorked - 9) * 96.2) End If ElseIf inAccrualType = "VAC" Then 'Check number of years worked If lngYearsWorked <= 4 Then 'Return 80.08 per year fcnCalculateAccruedTimeOff = lngYearsWorked * 80.08 ElseIf lngYearsWorked >= 5 And lngYearsWorked <= 14 Then 'Return 80.08 per year for the first 4 (320.32), then 120.12 for 'each year from 5 - 14. fcnCalculateAccruedTimeOff = 320.32 + ((lngYearsWorked - 4) * 120.12) ElseIf lngYearsWorked >= 15 Then 'Return 80.08 per year for the first 4 (320.32, 120.12 for years '5 - 14 (1201.2), and 160.12 per year for each year 14 and after. fcnCalculateAccruedTimeOff = 320.32 + 1201.2 + ((lngYearsWorked - 14) * 160.16) End If End If 'Exit before error handling Exit FunctionError_Handler: 'Return -1 fcnCalculateAccruedTimeOff = -1End Function

Does it need to be a function?
If I were confronted with this requirement, I believe rather than 'hard coding' rates into a function I would have a reference table with three columns; [start months], [end months], [rate]
join this table to your data to return the rate * months work where employee service months is between [start months] and [end months]

Well I was just going by the post I found that code on. I guess it doesn't have to be code. I just thought it would be easier that way. I don't mind having it hard coded. The chances of us changing the rates are slim to none.

Well if you did go the hard coding route, you could use a function like this:Private Function VacTime(iMonths as Integer) If iMonths < 3 Then VacTime = 0 Else If iMonths < 5 * 12 Then VacTime = iMonths * 6.67 Else If iMonths < 10 * 12 Then VacTime = iMonths * 8.00 Else If iMonths < 15 * 12 Then VacTime = iMonths * 10.00 Else If iMonths < 18 * 12 Then VacTime = iMonths * 11.33 Else If iMonths < 20 * 12 Then VacTime = iMonths * 12.00 Else If iMonths < 40 * 12 Then VacTime = iMonths * 13.33 Else VacTime = iMonths * 14.00 End IfEnd Function

Hi Ron,
That might work but the ranges you have are in years, not months. I need to have it look at their start date and calculate their accrual based on their start date as well.

Ex: Hired 03/03/2008 = 5 years on 03/03/2013. So they would accrue 6.67 hours for January and February of this year and then go to 8 hours per month for the balance of this year. So their total vacation time for 2013 would be 93.33 hours in total.

0

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

The ranges I put are multiplied by 12 so they end up being in months. However, based on your example, you apparently need a different function to come up with your result.

Would you always calculate their vacation time as of the year's end? So if you tried running this function anytime in 2014 for the person hired in 03/03/08, you want the function to return 96 hours?

If the person had 5 years on 03/28/2013 instead, would March be 8 hours or 6.67?

If my assumptions are correct, the following should work:

Private Function GetVacTime(dtHire As Date) Dim iMonths As Integer Dim dHours As Double iMonths = DateDiff("m", dtHire, DateSerial(Year(Date), 1, 31)) 'months as of January 31 For i = 1 To 12 dHours = dHours + GetMonthHours(iMonths) iMonths = iMonths + 1 Next GetVacTime = dHoursEnd FunctionPrivate Function GetMonthHours(iMonths As Integer) If iMonths < 3 Then GetMonthHours = 0 ElseIf iMonths < 5 * 12 Then GetMonthHours = 6.67 ElseIf iMonths < 10 * 12 Then GetMonthHours = 8 ElseIf iMonths < 15 * 12 Then GetMonthHours = 10 ElseIf iMonths < 18 * 12 Then GetMonthHours = 11.33 ElseIf iMonths < 20 * 12 Then GetMonthHours = 12 ElseIf iMonths < 40 * 12 Then GetMonthHours = 13.33 Else GetMonthHours = 14 End IfEnd Function

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…

Overview: This article:
(a) explains one principle method to cross-reference invoice items in Quickbooks®
(b) explores the reasons one might need to cross-reference invoice items
(c) provides a sample process for creating a M…

In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…

In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page.
The problem with sub-reports and headings:
Add a dummy group to the sub report using the expression =1:
Set the “Repeat Section” property of the dummy…