Query Parameters

  • Thread starter Thread starter Mark D.
  • Start date Start date
M

Mark D.

I am trying to create an employee turnover query. The table I am pulling from
has the employees name, hire date, and termination date. Of course all the
employees do not have termination dates and that is where I am running into
problems. I want the query to tell me who was terminated in the first quarter
and who is still employed. I don't want the employees we terminated last year
to show up. I set up the first parameter to show hire dates that are equal to
or less than 3/31/09. I set my second parameter to show termination dates
equal to or greater than 1/1/09. The query only shows me employees terminated
and no employees that are still working. I remove the termination date
parameter and shows me employees we let go last year which I do not want. Any
suggestions?
 
Try this --
SELECT [employees name], [hire date], [termination date]
FROM YourTable
WHERE ([hire date] <=
DateAdd("q",Format(Date(),"q")-1,DateSerial(Year(Date()),1,1))-1) And
([termination date]
DateAdd("q",Format(Date(),"q")-2,DateSerial(Year(Date()),1,1))-1);
Click to expand...

If you also want to see current employees then try this --
SELECT [employees name], [hire date], IIF([termination date] Is Null,
"Employed", ([termination date]) AS Termed_Employed
FROM YourTable
WHERE (([hire date] <=
DateAdd("q",Format(Date(),"q")-1,DateSerial(Year(Date()),1,1))-1) And
([termination date]
DateAdd("q",Format(Date(),"q")-2,DateSerial(Year(Date()),1,1))-1)) Or
Click to expand...
([termination date] Is Null);
 
Back
Top