Solved

Function for Personal Time Off in MS Access

Posted on 2014-09-21
11
210 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 50

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
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.

 
LVL 1

Author Closing Comment

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

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 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 40341276
No problem.

/gustav
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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.

685 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