|  | Case Select / Query | | |
| | | Secret Squirrel |  |
|  Posted: Mon Sep 01, 2008 3:52 am Post subject: Case Select / Query |  |
| |  | |
I'm using a case select to calculate vacation hours for my employees. Since
this is done in VBA behind a form I can't duplicate this function in a query.
Or can I? What I'm trying to do is create a query that lists the accrued
vacation time just like the case select does and then subtract the time used
from that accrued time and then append the results to a table. But before I
can create the append query I need to create a query that will do the same as
the case select. Any ideas on how this can be done?
Function VacHoursEarned(CalcYear As Variant) As Variant
If IsNull(CalcYear) Then
VacHoursEarned = Null
Exit Function
End If
Select Case CalcYear
Case Is < 2
VacHoursEarned = 0
Case 2 To 4
VacHoursEarned = 80
Case 5 To 9
VacHoursEarned = 96
Case 10 To 14
VacHoursEarned = 120
Case 15 To 19
VacHoursEarned = 128
Case 20 To 24
VacHoursEarned = 136
Case 25 To 29
VacHoursEarned = 144
Case 30 To 34
VacHoursEarned = 152
Case Is >= 35
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function |
| |
| | | bcap | |
| | Posted: Mon Sep 01, 2008 4:19 am Post subject: Re: Case Select / Query | |
| | | |
Assuming that CalcYear (or the equivalent thereof) is a field in one of your
tables or derivable therefrom, then you *can* simply call the function from
your query, something like this:
SELECT VacHoursEarned([CalcYear]) AS VacHours FROM....
However, in this situation I would be inclined to set up a table to help me.
It would have two columns, calc_year and vac_hours, and contain data like
this:
calc_year vac_hours
0 0
1 1
2 80
3 80
4 80
5 96
and so on...
Then, all you need to do is to join to the table to get the vacation hours,
which will be a lot more efficient than calling a user-defined function.
Plus, when some clever-dick decides to change the bands, all you need to do
is to update the table, you don't have to change any code or queries.
"Secret Squirrel" <secretsquirrel@discussions.microsoft.com> wrote in
message news:6F977515-648E-43D8-A23F-171A1C679044@microsoft.com...
| Quote: | I'm using a case select to calculate vacation hours for my employees.
Since
this is done in VBA behind a form I can't duplicate this function in a
query.
Or can I? What I'm trying to do is create a query that lists the accrued
vacation time just like the case select does and then subtract the time
used
from that accrued time and then append the results to a table. But before
I
can create the append query I need to create a query that will do the same
as
the case select. Any ideas on how this can be done?
Function VacHoursEarned(CalcYear As Variant) As Variant
If IsNull(CalcYear) Then
VacHoursEarned = Null
Exit Function
End If
Select Case CalcYear
Case Is < 2
VacHoursEarned = 0
Case 2 To 4
VacHoursEarned = 80
Case 5 To 9
VacHoursEarned = 96
Case 10 To 14
VacHoursEarned = 120
Case 15 To 19
VacHoursEarned = 128
Case 20 To 24
VacHoursEarned = 136
Case 25 To 29
VacHoursEarned = 144
Case 30 To 34
VacHoursEarned = 152
Case Is >= 35
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function |
|
| |
| | | Tom Wickerath | |
| | Posted: Mon Sep 01, 2008 4:29 am Post subject: RE: Case Select / Query | |
| | | |
Hi Secret Squirrel,
You can use the Switch function in a query to accomplish this. Something
like this for a SQL statement, where you replace YourTableNameHere with the
actual name of your table:
SELECT CalcYear,
Switch([CalcYear]<2,0,
[CalcYear]<=4,80,
[CalcYear]<=9,96,
[CalcYear]<=14,120,
[CalcYear]<=19,128,
[CalcYear]<=24,136,
[CalcYear]<=29,144,
[CalcYear]<=34,152,
[CalcYear]>=35,160)
AS [Vacation Hours Earned]
FROM [YourTableNameHere];
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Secret Squirrel" wrote:
| Quote: | I'm using a case select to calculate vacation hours for my employees. Since
this is done in VBA behind a form I can't duplicate this function in a query.
Or can I? What I'm trying to do is create a query that lists the accrued
vacation time just like the case select does and then subtract the time used
from that accrued time and then append the results to a table. But before I
can create the append query I need to create a query that will do the same as
the case select. Any ideas on how this can be done?
Function VacHoursEarned(CalcYear As Variant) As Variant
If IsNull(CalcYear) Then
VacHoursEarned = Null
Exit Function
End If
Select Case CalcYear
Case Is < 2
VacHoursEarned = 0
Case 2 To 4
VacHoursEarned = 80
Case 5 To 9
VacHoursEarned = 96
Case 10 To 14
VacHoursEarned = 120
Case 15 To 19
VacHoursEarned = 128
Case 20 To 24
VacHoursEarned = 136
Case 25 To 29
VacHoursEarned = 144
Case 30 To 34
VacHoursEarned = 152
Case Is >= 35
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function |
|
| |
| | | Tom Wickerath | |
| | Posted: Mon Sep 01, 2008 4:34 am Post subject: RE: Case Select / Query | |
PS.
I like bcap's solution better, where you set up a table to do this. It's
alot easier to maintain in the future.
Hey Bcap: "Plus, when some clever-dick decides..."
<Smile>
Tom Wickerath
Microsoft Access MVP
LINK
LINK |
| |
| | | John W. Vinson | |
| | Posted: Mon Sep 01, 2008 4:58 am Post subject: Re: Case Select / Query | |
On Sun, 31 Aug 2008 22:52:02 -0700, Secret Squirrel
<secretsquirrel@discussions.microsoft.com> wrote:
| Quote: | I'm using a case select to calculate vacation hours for my employees. Since
this is done in VBA behind a form I can't duplicate this function in a query.
Or can I?
|
Yes, you can - with a table of hours and a subquery. Create a table VacHours
with fields for top of the range and the corresponding hours:
Select Case CalcYear
Case Is < 2
VacHoursEarned = 0
Case 2 To 4
VacHoursEarned = 80
Case 5 To 9
VacHoursEarned = 96
Case 10 To 14
would correspond to records
Years; VacHoursEarned
2; 0
4; 80
9; 96
etc.
A calculated field
VacHoursEarned: (SELECT VacHoursEarned FROM VacHours AS V WHERE V.Years <
HoursEarned AND V.Years >= (SELECT Min(W.Years) FROM VacHours AS W WHERE
W.HoursEarned < V.HoursEarned))
should do the trick.
--
John W. Vinson [MVP] |
| |
| | | Secret Squirrel | |
| | Posted: Mon Sep 01, 2008 1:50 pm Post subject: RE: Case Select / Query | |
| | | |
Hi Tom,
While I like bcap's suggestion of a table I think it would be better if I
did this in a query. The reason is that if the employees are here less than 2
years then there is a quirky calculation used to calculate their vacation
time. I can duplicate your switch query and then union them all together to
form one query for all my employees. I know it might be a pain to change this
if the powers that be decide to overhaul the vacation accrual formula but I
kind of doubt they'll be doing that anytime soon. Thank you for suggestion!
It works perfectly!
SS
"Tom Wickerath" wrote:
| Quote: | Hi Secret Squirrel,
You can use the Switch function in a query to accomplish this. Something
like this for a SQL statement, where you replace YourTableNameHere with the
actual name of your table:
SELECT CalcYear,
Switch([CalcYear]<2,0,
[CalcYear]<=4,80,
[CalcYear]<=9,96,
[CalcYear]<=14,120,
[CalcYear]<=19,128,
[CalcYear]<=24,136,
[CalcYear]<=29,144,
[CalcYear]<=34,152,
[CalcYear]>=35,160)
AS [Vacation Hours Earned]
FROM [YourTableNameHere];
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Secret Squirrel" wrote:
I'm using a case select to calculate vacation hours for my employees. Since
this is done in VBA behind a form I can't duplicate this function in a query.
Or can I? What I'm trying to do is create a query that lists the accrued
vacation time just like the case select does and then subtract the time used
from that accrued time and then append the results to a table. But before I
can create the append query I need to create a query that will do the same as
the case select. Any ideas on how this can be done?
Function VacHoursEarned(CalcYear As Variant) As Variant
If IsNull(CalcYear) Then
VacHoursEarned = Null
Exit Function
End If
Select Case CalcYear
Case Is < 2
VacHoursEarned = 0
Case 2 To 4
VacHoursEarned = 80
Case 5 To 9
VacHoursEarned = 96
Case 10 To 14
VacHoursEarned = 120
Case 15 To 19
VacHoursEarned = 128
Case 20 To 24
VacHoursEarned = 136
Case 25 To 29
VacHoursEarned = 144
Case 30 To 34
VacHoursEarned = 152
Case Is >= 35
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function |
|
| |
|
|
