Function for Personal Time Off in MS Access

I need to create a function where employees automatically accrue 8 hours of personal time every ten weeks throughout the calendar year. They start the calendar year with 8 hours and then accrue another 8 every 10 weeks. Is there an easy way to create a function for this? Then when we enter a request for time off it will look to see if they have enough hours accrued to be able to approve that time off request. Any ideas on how this can be written? I'm not very good at writing VBA code so I'm turning to the experts here.

Larry
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
Here's one way to do it. In your employee table, you should have a start date. In a query you can use this formula to determine how much time the employee accrued:

PersonalTimeAccured: Int(DateDiff("w",[StartDate],Now())/10)*8

You can make a second table and log each date and the number of hours taken on that date by each employee. Make a query off that table and group the employees and sum the hours taken. Now make one more query and add the two you just made. Time remaining would be Time Accured - Time Used. See qryPersonalTimeRemaining in the attached db.

Flyster
PersonalTime.accdb
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That works great but at the end of every calendar year it goes back to 0. Whatever they don't use by the end of the year gets paid out and then they start back at 8hrs for the new year and then accrue 8hrs every 10 weeks through the year.
0
Gustav BrockCIOCommented:
Here's a method:

PersonalTimeAccured: Int(DateDiff("w", IIf(Year([StartDate]) = Year(Date()), [StartDate], DateSerial(Year(Date()), 1, 1))Date()) / 10) * 8

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you both for your help. It's greatly appreciated.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Not sure if I can re-open this question but there is one minor problem with the formula. At the beginning of the calendar year employees will start with 8hrs of personal time and then accrue 8hrs every 10 weeks throughout the year. The formula you wrote does the accrual correct and will reset at the start of every calendar year but it doesn't start the employees with 8hrs. Is there an easy way to modify your formula to have it start with 8hrs at the beginning of the calendar year instead of starting back at 0hrs?
0
Gustav BrockCIOCommented:
You could count from 10 days earlier if so:

Int(DateDiff("w", IIf(Year([StartDate]) = Year(Date()), [StartDate], DateAdd("d", -10, DateSerial(Year(Date()), 1, 1))), Date()) / 10) * 8

/gustav
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That didn't work. I'm still getting 24hrs for the current year when I should be getting 32hrs to this point in the year. Can't I just put "+8" at the end of your original formula to add 8 hours to start the year so instead of resetting to 0 at the start of every calendar year it will start at 8.
0
Gustav BrockCIOCommented:
I read it as 8 hours were only to be added at New Year.

If it counts for all, then just add 8:

Int(DateDiff("w", IIf(Year([StartDate]) = Year(Date()), [StartDate], DateSerial(Year(Date()), 1, 1))Date()) / 10) * 8 + 8

or add 1 to the day count:

(Int(DateDiff("w", IIf(Year([StartDate]) = Year(Date()), [StartDate], DateSerial(Year(Date()), 1, 1))Date()) / 10) +1 ) * 8

/gustav
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thanks. I guess I confused the subject by saying it should reset to 0 at the start of every year when in fact it should reset to 8 hours, not 0hrs.
0
Gustav BrockCIOCommented:
No problem.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.