Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

calclulate Employee Vacation days

Posted on 2010-08-19
5
Medium Priority
?
473 Views
Last Modified: 2012-05-10
Employee Vacation days:
Looking for a way to calculate employee vacation days left based on hire date and length of employment.
Condition: If employed for 6 month then entitled vacation days = 5, if more than 2 years then 6 days, more than 3 years then 7 days, more than 4 years then 8 days, more than 5 years then 9 days and if more than 10 years then 10 days.
I would like to calculate above for all employees with a click of a button.
0
Comment
Question by:nibirkhan
[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
5 Comments
 
LVL 2

Expert Comment

by:ctpmn1
ID: 33479705
You would create a query that would query a table with the emplyees Date of employement. Then you would use the date of employement to find the number of days worked and then run a simple If statement. If Days worked  >= 36500 (10 years) = 10 and so on.
0
 
LVL 2

Author Comment

by:nibirkhan
ID: 33479965
thanks ctpmn1 for your idea, I need a little bit more details, note that i am going to calculate for all the employees in one shot. I do know the idea as you mentioned but I need code or query statement.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 800 total points
ID: 33481607
<I do know the idea as you mentioned but I need code or query statement.>
First, despite it's apparent simplicity, this is not a simple task.

First, most companies calculate Vacation in Hours, not days.
Which make these calculations much simpler.
(Suppose someone takes 1 hour off to get married and wants it used as Vacation?)
;-)

You also have the issue of recalculating the exact number on the anniversary of the hire date.
Most Date functions in Access (ex. DateDiff() ) will "round" their values.

So again, it is not so simple to do this.

Here is a sample that simply takes the Difference between the Employees HireDate and Today's Date (in months and years) to calculate Vacation days.
(Using the DataDiff, in a function)
As I said, it may not be perfect, but it does work, and shows you the basics of how your specific system can be built.
;-)
You will have to study this carefully and adjust it where needed.

See the query: qryVacDays
(You can make a Report or a Form from this query)

;-)

JeffCoachman
Access-EEQ26416074Calculate-Vaca.mdb
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1200 total points
ID: 33481612
place this function in a regular module

in a Query, create something like this, assuming the hire date field is named HireDate

select [Name],HireDate,ComputeVacationDays([HireDate]) as DaysOfVacation
From EmployeeTable

(* change field name and table name accordingly)

save the query as QVacations

in a click of a button

Private sub Button_click()
docmd.openquery "Qvacations"
end sub










Function ComputeVacationDays(datHire As Date)
If VarType(datHire) <> 7 Then ComputeVacationDays = Null: Exit Function
Dim hDate As Date, varYear As Long
hDate = DateSerial(Year(Date), Month(Date), Day(datHire))
varYear = DateDiff("yyyy", datHire, Date) + (Date < hDate)
If varYear > 0 Then
    Select Case varYear
        Case Is >= 10: ComputeVacationDays = 10
        Case Is >= 5: ComputeVacationDays = 9
        Case Is >= 4: ComputeVacationDays = 8
        Case Is >= 3: ComputeVacationDays = 7
        Case Is >= 2: ComputeVacationDays = 6
        Case Is >= 1: ComputeVacationDays = 5
    End Select
    Else
    varYear = DateDiff("m", datHire, Date) + (Date < hDate)
    Select Case varYear
        Case Is >= 6: ComputeVacationDays = 5
        Case Else: ComputeVacationDays = 0
    End Select
End If
End Function

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33481663
nibirkhan,

take note about HireDate with day the same as the day you are going to run the Query values

2/19/2010  > is entitled to 5 days, starting today 8/19/2010

the function i posted computes the vacation days considering Day, Months and Years.. of HireDate

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

661 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