Solved

Function for Personal Time Off in MS Access

Posted on 2014-09-21
11
206 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Is it possible to reset DSum? 12 43
MS Access Order Smallest to Biggest Query Help 13 43
access 7 25
Access database form in matrix view 14 15
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 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

14 Experts available now in Live!

Get 1:1 Help Now