Solved

calclulate Employee Vacation days

Posted on 2010-08-19
5
465 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
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 200 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 119

Accepted Solution

by:
Rey Obrero earned 300 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 119

Expert Comment

by:Rey Obrero
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

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.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now