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
Larry
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for your help. It's greatly appreciated.
You are welcome!
/gustav
/gustav
ASKER
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?
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
Int(DateDiff("w", IIf(Year([StartDate]) = Year(Date()), [StartDate], DateAdd("d", -10, DateSerial(Year(Date()), 1, 1))), Date()) / 10) * 8
/gustav
ASKER
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.
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
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
ASKER
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.
No problem.
/gustav
/gustav
ASKER