1st of The Month

[Guest]

Hi
Here is the scenerio:

I want to run a query which will me a list of all employees eligible for
401-K enrollment as of last pay period. The criteria for eligibility is as
follows:

If an employee's hiredate is the 1st of the Month, then he/she is eligible
on the 1st day of the 3rd calender month after the hire date.

Example: If hire date = 07/01/06, then Eligibility date = 10/1/06

If an employee's hiredate is not the first of the Month, then he/she is
eligible on the 1st day of the 4th calender month after the hire date.

Example: If hire date = 07/02/06, then Eligibility date = 11/1/06


Can someone tell me how to build this query?

Thank you in advance for your help.

Yasser

 

[Guest]

Try this --
SELECT YasserEMP.EmpID, YasserEMP.HireDate,
IIf([HireDate]<=[HireDate]-Day([HireDate])+1,DateAdd("m",3,[HireDate]-Day([HireDate])+1),DateAdd("m",4,[HireDate]-Day([HireDate])+1))
AS [401K Eligibility date]
FROM YasserEMP;

 

[Guest]

Karl
Your query worked like magic!

Thanks again

Try this --
SELECT YasserEMP.EmpID, YasserEMP.HireDate,
IIf([HireDate]<=[HireDate]-Day([HireDate])+1,DateAdd("m",3,[HireDate]-Day([HireDate])+1),DateAdd("m",4,[HireDate]-Day([HireDate])+1))
AS [401K Eligibility date]
FROM YasserEMP;

Hi
Here is the scenerio:

I want to run a query which will me a list of all employees eligible for
401-K enrollment as of last pay period. The criteria for eligibility is as
follows:

If an employee's hiredate is the 1st of the Month, then he/she is eligible
on the 1st day of the 3rd calender month after the hire date.

Example: If hire date = 07/01/06, then Eligibility date = 10/1/06

If an employee's hiredate is not the first of the Month, then he/she is
eligible on the 1st day of the 4th calender month after the hire date.

Example: If hire date = 07/02/06, then Eligibility date = 11/1/06


Can someone tell me how to build this query?

Thank you in advance for your help.

Yasser