Marco based on date?

[AccessNoob]

Still reading my Access Bible. I have created a nice employee-tracking
program for my job and have even gotten it implemented.

I need to build a vacation time tracking macro/code


I have been trying to build a macro that will calculate who has accrued
what vacation time automatically.

I have several fields I am trying to use.

PTO accrual rate: 2.46 (vacation hours added once every two weeks)
Higher date: 10/06/05 (The day they started accruing vacation time)
Used PTO: 5 (How much vacation they have used..if any)
Available PTO time. (What is currently available?)

=sum([PTOrate]*[mystery calendar command, includes start date and
current date] - [UsedPTO]) and runs only every other Friday.

Right now, I just go in with a calculator every two weeks and add 2.46
hours on every employee's page. There has to be an easier way than
having to update 60 pages of info.

It is easy enough to just build a simple addition and subtraction macro
but I have no idea how to tie the function into a calendar.

Can anyone help? Or am I just going about this the wrong way?

Thanks
Noob

 

[Guest]

Hi, AccessNoob.

It seems you can either:

1) Calculate accrued vacation from the current date and the Hire date OR
2) Run an update query once every 2 weeks that adds 2.46 to each record

The problem with the second approach is that you would need to build in some
sort of protection to avoid either you or a vacation-hungry, dishonest
employee from running the query additional times.

To calculate the earned vacation from the HireDate and the current date, add
a calculated field expression into a query. Use the DateDiff function with
the "w" parameter to calculate the number of weeks since the date of hire,
and multiple it by 2.46 * .5:

AccruedVacation: =Int(DateDiff("w",Date(),[HireDate]) * 2.46 * .5)

, or if the accrual rate is in a field, because it's not a constant:

AccruedVacation: =Int(DateDiff("w",Date(),[HireDate) * [WeeklyAccrualRate])

Int truncates the decimal portion, giving you an even number of hours.

The Available hours can then be calculated from the AccruedVacation and the
used hours:

AvailablePTO: [AccruedVacation] - [UsedPTO]

Executing this query at any time will give you the available hours for each
employee, with you only adding data when someone uses vacation.

Hope that helps.
Sprinks

 

[John Vinson]

Still reading my Access Bible. I have created a nice employee-tracking
program for my job and have even gotten it implemented.

I need to build a vacation time tracking macro/code


I have been trying to build a macro that will calculate who has accrued
what vacation time automatically.

A Macro would not be the appropriate tool: a Query is much better for
this.

I have several fields I am trying to use.

PTO accrual rate: 2.46 (vacation hours added once every two weeks)
Higher date: 10/06/05 (The day they started accruing vacation time)
Used PTO: 5 (How much vacation they have used..if any)
Available PTO time. (What is currently available?)

=sum([PTOrate]*[mystery calendar command, includes start date and
current date] - [UsedPTO]) and runs only every other Friday.

Right now, I just go in with a calculator every two weeks and add 2.46
hours on every employee's page. There has to be an easier way than
having to update 60 pages of info.

Well... don't store variables. Store constants.

It is easy enough to just build a simple addition and subtraction macro
but I have no idea how to tie the function into a calendar.

Can anyone help? Or am I just going about this the wrong way?

So it would seem... but I cannot figure out from your post what your
table structure actually might be. If you're STORING Vacation Used and
Available PTO Time in your table... DON'T. Ideally you should have a
one-to-many relationship from a Personnel table to a Vacations table,
in which each record contains an episode of vacation for a particular
person; you can use a Totals query or a DSum() function call to sum
the actual time taken. Similarly, you can calculate the time available
by using DateDiff() to calculate the number of weeks since the start
of the accrual period and multiply by the appropriate factor.

John W. Vinson[MVP]