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
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