Absence Tracking - Access Developer Network


	Back to Access Developer Network Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Absence Tracking

Eric Wallace

5 years ago

I am working on an HR database application. We track time in 3 categories: PTO, sick and leave. Sick time and leave time are based on the calendar year. PTO however, is tracked by service date. Currently my design includes an employee table with relationship to the absence table that contains fields for employee, occurrence date, category and amount of time. I have a single form set up for entering occurrences. My goal is to set up a single form to display an individual employees occurrences with balances remaining. I am trying to build a table using a query to produce the appropriate start date for tracking PTO. Meaning, if today is 9/8/2021 and they reset 11/20/2021, the query needs to set the PTOreset date to 11/20/2020. I have tried DateAdd with my best guesses at an IIf statement in the query but, it either changes all years or leaves the fields blank. Any help or suggestions for another way are welcome. If I can get that piece right I think Ive learned enough to setup the queries for the end result form with totals using criteria but I just cant figure out the date problem. Any suggestions are welcome.
Thanks,
Eric

	Alex Hedley @Reply 5 years ago
Can you share what you've tried code wise?

	Adam Schwanz @Reply 5 years ago
What are you trying to have happen? How does service date affect the PTO reset date? I'm assuming you store a "start date" in the employee table? What's stopping you from using that or is there some other strange calculation that you can't just use an if statement to pick the current year if todays date is before the start date or current year+1 (next year) if todays date is after the start date?

Eric Wallace OP @Reply

5 years ago

Yes, I have a start date in the employee table and that is the date I am trying to manipulate. PTO balances reset to their starting amount on their start date and I am working towards taking the start date and changing it to the proper PTO reset date. In the example today being 9/8/2021 and their start date was 11/20/1997, I want the query to take 11/20/1997 and change it to 11/20/2020 as that was the last reset of PTO. I have tried several things like formatting the start date to current year as [ServiceDate] in a new field and then tried something like this: PTOResetDate: IIf([ServiceDate]>=Date(), DateAdd([ServiceDate], "yyyy"-1). I had planned to run this as a make table query to then use the PTO reset date as criteria on the single form with calculations to keep running totals of use/balances remaining. I am probably overcomplicating this but, hopefully the elaboration makes sense...

	Adam Schwanz @Reply 5 years ago

	Adam Schwanz @Reply 5 years ago

	Adam Schwanz @Reply 5 years ago
Thats almost certainly not the cleanest way to do it, but it works

	Adam Schwanz @Reply 5 years ago
That's their last reset date, which I think is what you're wanting?

	Eric Wallace OP @Reply 5 years ago
Thanks Adam! That looks to be exactly what I was trying to get to.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Network.

Next Unseen

New Feature: Comment Live View