How to manipulate dates to reflect current year anniversary

[jayd77]

I need to manipulate a hire date to reflect an employee's current anniversary date in a different column of a query. This is easy to do for the first year, just add 365 to the hire date. After that though, I need to now how many years have passed since the hire date so I can properly add the right number of years to get the current anniversary date. Any help would be much appreciated!

 

[dk]

Hi,

Check the DateDiff function in the help file.

 

[Rocky E]

Hi Jay,

With all resplect to Dan I think you want a solution that will be self maintaining. Access has a full set of date functions. Date() will return the current system date and year(date()) will return the current system year. If the your hire date is stored in a variable named hiredate and is set to #12/25/65# the following will hold. month(hiredate) will return a 12, day(hiredate) will return a 25 and year(date()) will return 2003. Now put that together. Anniversary = month(hiredate) & "/" & day(hiredate) & "/" & year(date()) will return 12/25/2003!

Now if you want use DateDiff("yyyy", hiredate, Date()) will return the number of years as of today, or DateDiff("yyyy" hiredate, Anniversary) for the number of years on his anniversary.

BTW all of this can be done in the immediate window for testing.

 

[Russell Hauf]

The easiest way I can think of is using the DateSerial function:

Say the anniversary date is called dtAnniv. In a blank column in your query add:

• dtAnnThisYr: DateSerial(Year(Date()),Month([dtAnniv]),Day([dtAnniv]))

If by "current anniversary date" you mean the NEXT anniversary date (so if the anniversary date passed yesteray, you want the next one), you can do something like this:

• dtNextAnniv: IIF(DateSerial(Year(Date()),Month([dtAnniv]),Day([dtAnniv])) > Date(), DateSerial(Year(Date())+1,Month([dtAnniv]),Day([dtAnniv])),DateSerial(Year(Date()),Month([dtAnniv]),Day([dtAnniv])))

or:

• DateSerial(Year(Date())+(Abs(DateSerial(Year(Date()),Month([dtAnniv]),Day([dtAnniv])) < Date())),Month([dtAnniv]),Day([dtAnniv]))

No addition necessary!

HTH,

Russell