vacation anniversary

[rlholland]

I have a vacation table that shows yearly vacation days, days used, and days
left. When their hire date (month and day) arrives I would like for a query
to change the yearly vacation days back to what they get yearly. Is there a
way to do this in an expression.

 

[Piet Linden]

I have a vacation table that shows yearly vacation days, days used, and days
left.  When their hire date (month and day) arrives I would like for a query
to change the yearly vacation days back to what they get yearly.  Is there a
way to do this in an expression.

use an update query.

UPDATE staff_vacation_days
SET vacation_days = [either a number or some kind of expression or
function]
WHERE employee_id = ...

a few options:
-- using a VBA function that you write to determine the number of
vacation days

UPDATE staff_vacation_days
SET vacation_days = fCalculateVacation(lngEmployeeType, dtStartDate)
WHERE employee_id = ...

using an IIF statement...

UPDATE staff_vacation_days
SET vacation_days = iif(employee_position='President', 25, iif
(employee_position ='grunt',2,10))
WHERE employee_id = ...

 

[Jack Cannon]

Assuming that you are using a SELECT query then the answer is NO.
You need to update a table every year and keep the appropriate in that table
by year.
Once you do that it is a simple matter of using the last year as the
appropriate source of information for your query.

Jack Cannon

 

[rlholland]

OK. My fields are [Vacation Days per Year] 5 per year after 1 year, 10 per
year after 5 years, 15 per year after 15 years and 20 per year after 20
years. [Days Used] Sum. [Days Left] expression. [Hire Date]mm,dd,yyyy. Can
you please tell me what I need put in the update query for the [Vacation days
per year to change on there anniversary. Sorry, I'm not very good at the
update querries.
Thanks in advance.

I have a vacation table that shows yearly vacation days, days used, and days
left. When their hire date (month and day) arrives I would like for a query
to change the yearly vacation days back to what they get yearly. Is there a
way to do this in an expression.

use an update query.

UPDATE staff_vacation_days
SET vacation_days = [either a number or some kind of expression or
function]
WHERE employee_id = ...

a few options:
-- using a VBA function that you write to determine the number of
vacation days

UPDATE staff_vacation_days
SET vacation_days = fCalculateVacation(lngEmployeeType, dtStartDate)
WHERE employee_id = ...

using an IIF statement...

UPDATE staff_vacation_days
SET vacation_days = iif(employee_position='President', 25, iif
(employee_position ='grunt',2,10))
WHERE employee_id = ...