Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

calclulate Employee Vacation days

Posted on 2010-08-19
5
Medium Priority
?
474 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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