Case Select - Vacation Accruals

S

Secret Squirrel

I have a case select I'm using to calculate vacation hours earned. The
problem I'm having (or the way my company does vacation accruals) is if any
employee is going to hit one of the next levels up within that current year
then they get that level's vacation amount.

For example:

Hire date: 12/01/06

Now they haven't been here for 2 years yet but since they're 2 years is
within the current year they get 80 hours instead of the 40 hours. How do I
have this case select look at the start date on my form to figure this out?
So back on 1/1/08 when the vacation accruals were set up this employee would
have 80 hours.



Function VacHoursEarned(varYears As Variant) As Variant

If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function
End If
Select Case varYears
Case 0 To 0.9
VacHoursEarned = 0
Case 1 To 1.9
VacHoursEarned = 40
Case 2 To 4.9
VacHoursEarned = 80
Case 5 To 9.9
VacHoursEarned = 96
Case 10 To 14.9
VacHoursEarned = 120
Case 15 To 19.9
VacHoursEarned = 128
Case 20 To 24.9
VacHoursEarned = 136
Case 25 To 29.9
VacHoursEarned = 144
Case 30 To 34.9
VacHoursEarned = 152
Case Is >= 35
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function
 
S

Secret Squirrel

Hi Steve,
I'm not sure I understand what you mean. It's not 2 years as a default. If
within the current calendar year they are going to be changing levels based
 
J

John... Visio MVP

Just subtract the year of hir efrom the current year. That should give you
the number you need for your function.

John... Visio MVP
 
J

John Spencer

What is VarYears? How is it calculated? Is it partial years or whole years?

For instance you could use the dateDiff function to get the number you pass to
as the argument to the function.
DateDiff("YYYY",[Hire Date],Date())

For your specific example that is going to return 2.

It will return two for anyone hired in 2006 when you calculate the value on
any date in 2008.

Another expression (Very similar)
Year(Date()) - Year([Hire Date])

And your case statement could be rewritten so you would not have possible
missed values. I don't know if it is possible that you would get a value of
1.97 to pass into the function, but if you did the function would return Null
instead of 40 (or 80)

Select Case varYears
Case is < 1
VacHoursEarned = 0
Case is <2
VacHoursEarned = 40
Case is <5
VacHoursEarned = 80
Case is < 10
VacHoursEarned = 96
Case is <15
VacHoursEarned = 120
Case is < 20
VacHoursEarned = 128
Case is <25
VacHoursEarned = 136
Case is < 30
VacHoursEarned = 144
Case is < 35
VacHoursEarned = 152
Case Is >= 35
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top