Annual Review

[kwilbur]

Hello all and Happy New Year,

I am trying to have a live tracker for annual reviews. I have hire dates that range from 1981 to 2012. My goal is to have their month and day from their Hire Dates and have the year refresh. ie if someone is hired on the 2nd of Jan their review date would be 1/2/2013 or if they were hired Jan 4th their review date would be 1/4/2012.

I use those examples because I thought I had those down until I saw the Jan 2nd date as a 2012 instead of 2013. I have a column set up in a Query called NextReviewDate. Please help. Thanks in advance.

[dblife]

Have you thought about the DateAdd() function?
http://www.techonthenet.com/access/f...te/dateadd.php

[kwilbur]

Date Add function wouldn't be sufficient enough. Since all EE's do not have the same Hire Date. I was looking for more of an efficient way to do this. Currently my process is using the DatePart function in two columns one for Month and one for Day. A third column to combine the month and day back to form a mm/dd. Another column to use the DateValue to have the current year placed. And lastly the final column for an iif function that adds a year to the DateValue column if the date is < Date().

This is a long way to do it. If one exists, I would like a shorter way.

Maybe sample data would help: The next Review Date Column is what I envision.

EE ID..................Hire Date.................Next Review Date
1.......................1/2/1981..................1/2/2013
2.......................1/4/2001..................1/4/2012
3.......................10/14/2007...............10/14/2012

[jzwp11]

You will need to nest the IIF() function within the dateserial() function, something like this:

dateserial(IIF(date()>dateserial(year(date()),mont h(hiredate), day(hiredate)),year(date())+1,year(date())), month(hiredate), day(hiredate))

[kwilbur]

thanks jzwp. worked like a charm. it seems like im the only one on the internet with this problem. couldn't find it anywhere.

[jzwp11]

You're welcome. I would guess that your problem was not unique to you, so perhaps others looking for a similar solution will benefit from this thread.