Solved

calclulate Employee Vacation days

Posted on 2010-08-19
5
464 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

Complete Microsoft Windows PC® & Mac Backup

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.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

760 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

18 Experts available now in Live!

Get 1:1 Help Now