Filling text box values

[robert693]

I have a database with two tables. One has employee information the other days of leave an employee takes off. They are related by the employee ID number. An employee gets 20 days off each year, after 5 years they get 25. They can take full days and half days. I have a form that shows the employee information, with a subform showing the days off the employee has taken along with information about why. Every year on the anniversary of the employee's hire date the number of days the employee has left to take off rolls back to 20. I want to have two text boxes to show the current year for each employee. One will have the most recent anniversary date, the beginning of his year. The other will have the next anniversary date, the end of the employee's current year. I was wondering if I have to fill these fields manually or if there is something programmatic I can do to fill them.

 

[jimbrooking]

You could write a loop using the DateAdd function to cycly through 1-year increments, exiting when the DateAdd value exceeds Now(). If N is your counter, then on exit, N years from the employee's anniversary date would be the NEXT anniversary date, and N-1 years would be the last anniversary date before today.

Barring syntax errors, the code might look something like
'---Start Code---
Dim N as Integer
For N = 1 to 100 ' or some reasonable number
If DateAdd("yyyy", N, [EmloyeeStartDate]) > Now() then Exit For
Next N

If N = 100 then MsgBox("This employee seems to have been with us for 100 years. Check Life Signs.",,"Huh???"

Me.tbxNextAnnDate = DateAdd("yyyy", N, [EmloyeeStartDate])
Me.tbxLastAnnDate = DateAdd("yyyy", N-1, [EmloyeeStartDate])
'---End Code---

If the employee has been there less than a year, the LastAnnDate tbx would = his/her start date.

Jim

 

[dhoffman]

why do a loop? why not just grab the hire date, and swap the year portion with the current year. Then if that date is less than Now() that is the last time vacation was reset for that employee. Next time is exactly one year later. If the date is greater than Now() it will be the next time and the last time was exactly one year behind . I can't remember off hand how to manipulate individual parts of a date but it's related to that DateAdd function the last guy mentioned. Don't forget to handle for the case when the actual day (Now()) is actually an anniversary of the employee's hire date.