Eligibility Date question

G

Guest

I need to show in a report when an employee will be eligible for a certain
benefit. The eligibility is basically the first day of the first calendar
quarter AFTER one complete year of employment. For example, if a person's
hire date is 9/5/2003, he becomes eligible on 10/1/2004, which is one year
(9/5/2003 to 9/5/2004), plus the first day of the next quarter, which is
10/1/2004. If his hire date is 10/1/2004, he won't be eligible until
1/1/2006, which is one year (10/1/2004 to 10/1/2005), plus the start of the
next quarter which is January of 1006. Does anyone have any idea how I can
accomplish this using [HireDate] as the starting point? I hope this is clear!
 
S

Steve Schapel

Susan

Try it like this...

Eligibility:
DateSerial(Year([HireDate])+1-(Month([HireDate])>9),(DatePart("q",[HireDate])*3+1)
MOD 12,1)
 
G

Guest

Perfect! Thanks very much, Steve!

Steve Schapel said:
Susan

Try it like this...

Eligibility:
DateSerial(Year([HireDate])+1-(Month([HireDate])>9),(DatePart("q",[HireDate])*3+1)
MOD 12,1)

--
Steve Schapel, Microsoft Access MVP
I need to show in a report when an employee will be eligible for a certain
benefit. The eligibility is basically the first day of the first calendar
quarter AFTER one complete year of employment. For example, if a person's
hire date is 9/5/2003, he becomes eligible on 10/1/2004, which is one year
(9/5/2003 to 9/5/2004), plus the first day of the next quarter, which is
10/1/2004. If his hire date is 10/1/2004, he won't be eligible until
1/1/2006, which is one year (10/1/2004 to 10/1/2005), plus the start of the
next quarter which is January of 1006. Does anyone have any idea how I can
accomplish this using [HireDate] as the starting point? I hope this is clear!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top