• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

Vacation Accrual Calculation using MS-Access

I am trying to construct an Access database that will allow for the calculation of vacation days accrued for employees using the criterion below

Status = Established
under $25,692 per year = 21 days (1.75 per month)
$25,692 and under $35,544 per year = 28 days (2.3 per month)
$35,544 per year and over = 35 days (2.9 per month)

Status = Un-established
1 - 2 years service = 14 working days
3 - 5 years service = 21 working days
6 - 10 years service = 28 working days
over 10 years service= 35 working days

Status = Contracted
under $24,000 per year = 15 working days
$24,000 to $41,988 per year = 20 working days
over $41,988 per year = 25 working days

It is hoped that the employee status and salary would be somehow used to determine the vacation days they are entitled to. The accrued days will also be deducted from holidays granted to employees and recorded in a calculated filed to ensure an accurate balance is kept. Can anyone help?

The basic staff table has the following fields:
Start Date
Gerard James
Gerard James
3 Solutions
Are you planning to run the accrual at the end of each month?  What salary and status would you use?  The ones effective on the first day of the month or the ones effective on the last day of the month?  Why use both status and salary?  What if they conflict?

You need a table to store vacation accrued and vacation used.  So each month a new accrual record would be added and each time the employee uses vacation time a new record would be added using negative amounts so the table can be summed to get the current balance for an employee.

What have you got so far?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
here is a general function you can call to calculate vacation days. If your logic to calculate years of service is different, you can modify the code.
Function GetVacationDays( _
              psStatus As String _
             , pcSalary As Currency _
             , pDtStart As Date _
             , Optional pdblDaysPerMonth As Double _
                ) As Integer

'160902 strive4peace
   '  psStatus is the status
   '  pcSalary is the salary
   '  pDtStart is the start date
   '  pdblDaysPerMonth is returned -- send if you want it back
   '  working days of vacation
   'set up error handler
   On Error GoTo Proc_Err
   'initialize return value
   GetVacationDays = 0
   pdblDaysPerMonth = 0
   'dimension variables
   Dim iYearsService As Integer
   'calculate years of service
   iYearsService = DateDiff("yyyy", pDtStart, Date)
   'subtract 1 from years of service if start date is later in the year than today
   If DateSerial(Year(Date), Month(pDtStart), Day(pDtStart)) > Date Then
      iYearsService = iYearsService - 1
   End If
   Select Case psStatus
   Case "Established"
      If pcSalary >= 35544 Then
         pdblDaysPerMonth = 2.9
         GetVacationDays = 35
      ElseIf pcSalary >= 25692 Then
         pdblDaysPerMonth = 2.3
         GetVacationDays = 28
         pdblDaysPerMonth = 1.75
         GetVacationDays = 21
      End If
   Case "Un-established"
      If iYearsService >= 10 Then
         GetVacationDays = 35
      ElseIf iYearsService >= 6 Then
         GetVacationDays = 28
      ElseIf iYearsService >= 3 Then
         GetVacationDays = 21
         GetVacationDays = 14
      End If
   Case "Contracted"
      If pcSalary >= 41988 Then
         GetVacationDays = 25
      ElseIf pcSalary >= 24000 Then
         GetVacationDays = 20
         GetVacationDays = 15
      End If
   End Select 'status
   On Error Resume Next
   Exit Function
  'comment next line with MsgBox after this works ok so it just exits if there is an error
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetVacationDays"

   Resume Proc_Exit
End Function

Open in new window

Dale FyeCommented:
generous vacation plan, can I come work for you?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Gerard JamesAuthor Commented:
Thank you so much for your speedy response but I am afraid that I am still a bit lost. I have been trying to run the function in a query but not sure of the syntax and getting no results. I am sure that the error is on my part - can anyone assist.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
in a query, try this:
MyColumnName: GetVacationDays( [Status], [Salary], [Start Date],0)

Open in new window

MyColumnName is whatever you want to call the column (perhaps VacationDays)

As written, you will not be able to get the DaysPerMonth returned since it will require launching from code to grab a second value.

to launch from a query,
Optional pdblDaysPerMonth
may need to change to
Optional ByVal pdblDaysPerMonth
... if it can even be included

be sure to Debug,Compile, and Save the module before you use the function
Gerard JamesAuthor Commented:
So far I have nothing. I was hoping to get a semi-complete solution that could be modified to fit my needs.

The accrual should run at the end of each month and would be based on both the employee status and the salary earned. A separate table is required to document the running balance and all deductions made over time (something like a sub-form I guess).

Any help that can be provide inclusive of the additional table structures will be greatly appreciated. I am an Access rookie and this solution is gravely needed. Thanks again for your help.

Thanks Crystal.. I really do appreciate the solution provided.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Gerard,

> "Access rookie"

I guess the VBA code is kind of like being in the ocean when you don't know how to swim ...

instead of using a query to start ... maybe a form? Create a command button on the form and set these properties:

Name = cmdTest
Caption = Test

when you are in the Design View of a form, whatever you click on will show values in the Property sheet. Explore what you can set.

If the Property sheet is not displayed, press Alt-Enter to turn it on

Once you have created the command button ... that actually doesn't do anything yet, post back for the next step
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
poster was lost implementing VBA
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now