Almost There - Dates

[Daryl]

I am having fits with the empty fields and would appreciate any help. I am trying to identify employees who were actively employed on a certain date. I'm able to generate a list if they have been terminated. I can't seem to get those employee's who don't have a termination date (they are still hired). Here's the basic query:

PARAMETERS [Effective Date] DateTime;
SELECT tbl_FormValidity.[Employee ID], [Effective Date] AS [Effective Date], tbl_FormValidity.Hired, tbl_FormValidity.Terminated
FROM tbl_FormValidity
WHERE ((([Effective Date]) Between [tbl_FormValidity]![Hired] And [tbl_FormValidity]![Terminated]));

Thank you.

 

[selenau837]

I am having fits with the empty fields and would appreciate any help. I am trying to identify employees who were actively employed on a certain date. I'm able to generate a list if they have been terminated. I can't seem to get those employee's who don't have a termination date (they are still hired). Here's the basic query:

PARAMETERS [Effective Date] DateTime;
SELECT tbl_FormValidity.[Employee ID], [Effective Date] AS [Effective Date], tbl_FormValidity.Hired, tbl_FormValidity.Terminated
FROM tbl_FormValidity
WHERE ((([Effective Date]) Between [tbl_FormValidity]![Hired] And [tbl_FormValidity]![Terminated]));

Thank you.

Since you are looking for employees still employed, why not use the between dates on the Hired field.

WHERE ((([Effective Date]) Between [tbl_FormValidity]![Hired] And [tbl_FormValidity]![Hired]));

That way, you can get a date span based on the hired date.

Example. Give me all employees who were hired between 1/1/06 and 3/1/06.

Is that what you were looking for?

 

[Daryl]

Unfortunately, I wish it were that simple. Legal needs to have everyone (all employees) who was employed on a specified date. A fishing expedition if you ask me. I've tried using >=Effective Date (I'll have to change that to Employed Date) in the Hired field and <Effective Date in the Termination field. Because of the null or empty value in the Termination field, I can't get at current employees who were employed on the requested date.

 

[raskew]

Hi -

Try this as your WHERE statement

WHERE
   (((tbl_FormValidity.Hired)<=[Effective Date]) 
AND
   ((tbl_FormValidity.Terminated) Is Null 
OR
   (tbl_FormValidity.Terminated)>=[Effective Date]));

HTH - Bob