|  | Case Select - Vacation Accruals | | |
| | | Secret Squirrel |  |
|  Posted: Tue Aug 26, 2008 10:01 pm Post subject: Case Select - Vacation Accruals |  |
| |  | |
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 |
| |
| | | Steve | |
| | Posted: Tue Aug 26, 2008 10:49 pm Post subject: Re: Case Select - Vacation Accruals | |
| | | |
You could write an expression that adds 2 years to the hire date and then
looks to see if that date is equal to or less that December 31 of the
current year.
Steve
"Secret Squirrel" <secretsquirrel@discussions.microsoft.com> wrote in
message news:E40BCE44-AEA1-4629-AF94-35D485654A14@microsoft.com...
| Quote: | 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 |
|
| |
| | | Secret Squirrel | |
| | Posted: Tue Aug 26, 2008 11:00 pm Post subject: Re: Case Select - Vacation Accruals | |
| | | |
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
on my case select then I need it to select the higher level since they will
be converting within that year.
"Steve" wrote:
| Quote: | You could write an expression that adds 2 years to the hire date and then
looks to see if that date is equal to or less that December 31 of the
current year.
Steve
"Secret Squirrel" <secretsquirrel@discussions.microsoft.com> wrote in
message news:E40BCE44-AEA1-4629-AF94-35D485654A14@microsoft.com...
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
|
|
| |
| | | John... Visio MVP | |
| | Posted: Wed Aug 27, 2008 9:20 am Post subject: Re: Case Select - Vacation Accruals | |
Just subtract the year of hir efrom the current year. That should give you
the number you need for your function.
John... Visio MVP
"Secret Squirrel" <secretsquirrel@discussions.microsoft.com> wrote in
message news:1E50DC6A-B92B-4A10-94C1-7FC4059CC39A@microsoft.com...
| Quote: | 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
on my case select then I need it to select the higher level since they
will
be converting within that year.
"Steve" wrote:
You could write an expression that adds 2 years to the hire date and then
looks to see if that date is equal to or less that December 31 of the
current year.
Steve |
|
| |
| | | John Spencer | |
| | Posted: Wed Aug 27, 2008 10:01 am Post subject: Re: Case Select - Vacation Accruals | |
| | | |
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
Secret Squirrel wrote:
| Quote: | 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 |
|
| |
|
|
