Case Select / Query

[Secret Squirrel]

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]

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.

 

[Tom Wickerath]

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
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

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
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html

 

[John W. Vinson]

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.

 

[Secret Squirrel]

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

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
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

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