Vacation database, two timelines, still working on switch query

E

eb1mom

As suggested here I made two switch queries but, after many attempts I have
not been able to work out a way to select correct query to use. If 8 years
service by 1-30-2005 then 80 hours vacation. If not use NewWTime and they
would only get 40 hours at 8 years. I have read all I could find about select
case. Any suggestions on further reading or where I could find examples,
would be appreciated. Thank-you
Queries
WTime: (DateDiff("d",[HireDate],#1/30/2005#))/365
VacHrs: Switch([wtime]<1,"0",[wtime] Between 1 And
3,"40",[wtime]<=8,"80",[wtime]<=12,"120",[wtime]<=15,"144",[wtime]<=17,"152",[wtime]<=23,"160",[wtime]<=28,"200",[wtime]>28,"240")

NewWTime: (DateDiff("d",[HireDate],Now()))/365
NewVacHrs: Switch([NewWTime]<1,"0",[NewWTime] Between 1 And
3,"40",[NewWTime]<=10,"80",[NewWTime]<=20,"120",[NewWTime]<=30,"160",[NewWTime]>30,"200")
 
D

Duane Hookom

You should place this logic into a user-defined function, not in an
expression in a query.

Save the function in a module named "basBusinessCalcs" or something similar.
 
E

eb1mom

Duane Hookom said:
You should place this logic into a user-defined function, not in an
expression in a query.

Save the function in a module named "basBusinessCalcs" or something similar.

--
Duane Hookom
MS Access MVP


eb1mom said:
As suggested here I made two switch queries but, after many attempts I
have
not been able to work out a way to select correct query to use. If 8 years
service by 1-30-2005 then 80 hours vacation. If not use NewWTime and they
would only get 40 hours at 8 years. I have read all I could find about
select
case. Any suggestions on further reading or where I could find examples,
would be appreciated. Thank-you
Queries
WTime: (DateDiff("d",[HireDate],#1/30/2005#))/365
VacHrs: Switch([wtime]<1,"0",[wtime] Between 1 And
3,"40",[wtime]<=8,"80",[wtime]<=12,"120",[wtime]<=15,"144",[wtime]<=17,"152",[wtime]<=23,"160",[wtime]<=28,"200",[wtime]>28,"240")

NewWTime: (DateDiff("d",[HireDate],Now()))/365
NewVacHrs: Switch([NewWTime]<1,"0",[NewWTime] Between 1 And
3,"40",[NewWTime]<=10,"80",[NewWTime]<=20,"120",[NewWTime]<=30,"160",[NewWTime]>30,"200")
Click to expand...


Thank-you, I will work in the new direction you have suggested. eb1mom
Click to expand...
 
Top