?
Solved

Vacation Accrual Function In Access

Posted on 2013-11-20
9
Medium Priority
?
488 Views
Last Modified: 2013-11-22
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 Function
    
Error_Handler:

    'Return -1
    fcnCalculateAccruedTimeOff = -1
    
End Function

Open in new window

0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 1

Expert Comment

by:mattd_br
ID: 39662245
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]
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39662250
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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39662338
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 If

End Function

Open in new window

Ron
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39662399
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39662715
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?
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 39662801
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 = dHours
    
End Function

Private 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 If

End Function

Open in new window

MsgBox GetVacTime(#3/28/2008#) returns the value 93.34
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39664553
Hi Ron,

To answer your questions...

Yes we would calculate it as of the year's end. That way they have that time to use for the year.

If they reached 5 yrs on 3/28/13 they would still be 8 hours for match since it's within their hire month.

I'm going to test your function now. Thank you again for your help

Larry
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 39668501
Worked like a charm. Thank you Ron!
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39669032
Your welcome.
:-)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question