Solved

Function for Personal Time Off in MS Access

Posted on 2014-09-21
11
207 Views
Last Modified: 2014-09-24
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
0
Comment
Question by:Lawrence Salvucci
  • 5
  • 5
11 Comments
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 250 total points
ID: 40335863
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 40336207
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 40336504
Here's a method:

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

/gustav
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 40336695
Thank you both for your help. It's greatly appreciated.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40336697
You are welcome!

/gustav
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 40341223
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40341233
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 40341245
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40341253
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 40341265
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40341276
No problem.

/gustav
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question