Solved

Function for Personal Time Off in MS Access

Posted on 2014-09-21
11
204 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now