Find employees within a stated period

[CW]

I need a query to find employees who were with us during a selected period.
My table includes EmployeeName, StartDate and LeaveDate, and I know how to
use a parameter to set the date period. But I can't work out the exact
criteria to find the following employees:
StartDate is before the first date parameter
LeaveDate is either null OR after the first date parameter OR before the
second date parameter
Can somebody put me on the right track, please?
Many thanks
CW

 

[CompGeek78]

I need a query to find employees who were with us during a selected period.
My table includes EmployeeName, StartDate and LeaveDate, and I know how to
use a parameter to set the date period. But I can't work out the exact
criteria to find the following employees:
StartDate is before the first date parameter
LeaveDate is either null OR after the first date parameter OR before the
second date parameter
Can somebody put me on the right track, please?
Many thanks
CW

Your criteria will return all records. Suppose your first parameter is
6/1/2000 and your second parameter is 6/30/2000. Your criteria is date

6/1/2000 OR date < 6/30/2000. This matches all dates; 1/1/2000 is

less than 6/30 and 12/31 is greater than 6/1. Do you mean Leave date
is null OR after the first date parameter AND before the second
parameter?

Keven Denen

 

[CW]

Keven -
Thanks, yes I think your correction is right.
My problem is, how do I express those criteria so that they relate to the
dates entered in the parameter when the query is run? (What is the syntax?)
Many thanks
CW

 

[Ray]

Hi,

This assumes that Date1 and Date2 is the date range you are looking for:

SELECT * FROM Table
WHERE StartDate < Date1
AND (LeaveDate = Null OR LeaveDate Between Date1 and Date2)