Date range in query

[ladybug via AccessMonster.com]

I have a query from tblEmployeeSupervisorJctn. In this table there are 4
fields: chrSupervisorID, chrEmployeeUserID, dtmBeginDate, and dtmEndDate.

This table caputes the begin date: the date that employee started reporting
to that supervisor.
and end date: the date that employee no longer
reports to that supervisor.

Now, I want my query to have criteria where we put in the Supervisor Id and
then a date range. I then want returned who reported to that supervisor
during that time frame.

This is what I have so far: SELECT tblEmployeeSupervisorJctn.chrSupervisorID,
tblEmployeeSupervisorJctn.chrEmployeeUserID, tblEmployeeSupervisorJctn.
dtmBeginDate, tblEmployeeSupervisorJctn.dtmEndDate
FROM tblEmployeeSupervisorJctn
WHERE (((tblEmployeeSupervisorJctn.chrSupervisorID)=[Enter Supervisor ID]));

How do I do the date range in the criteria to capture through begin and end
date?

 

[John Spencer]

SELECT tblEmployeeSupervisorJctn.chrSupervisorID
, tblEmployeeSupervisorJctn.chrEmployeeUserID
, tblEmployeeSupervisorJctn.dtmBeginDate
, tblEmployeeSupervisorJctn.dtmEndDate
FROM tblEmployeeSupervisorJctn
WHERE chrSupervisorID=[Enter Supervisor ID]
AND dtmEndDate >= CDate([Start of Period])
AND dtmBeginDate <= CDate([End of Period])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[ladybug via AccessMonster.com]

That is almost perfect!! The only thing it is not capturing is if there is
no end date. No end date would mean that the employee still reports to that
supervisor.

If I put in 01/01/2008 for Start of Period
and then 04/01/2008 for End of Period

I have the following entries that are not being returned, but need to be:
chrSupervisorID chrEmployeeUserID dtmBeginDate
dtmEndDate
JUL4125 HFT1255 01/26/2007
JUL4125 LOS6334 03/24/2008

THANK YOU SO MUCH FOR HELPING ME!

SELECT tblEmployeeSupervisorJctn.chrSupervisorID
, tblEmployeeSupervisorJctn.chrEmployeeUserID
, tblEmployeeSupervisorJctn.dtmBeginDate
, tblEmployeeSupervisorJctn.dtmEndDate
FROM tblEmployeeSupervisorJctn
WHERE chrSupervisorID=[Enter Supervisor ID]
AND dtmEndDate >= CDate([Start of Period])
AND dtmBeginDate <= CDate([End of Period])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query from tblEmployeeSupervisorJctn. In this table there are 4
fields: chrSupervisorID, chrEmployeeUserID, dtmBeginDate, and dtmEndDate.

[quoted text clipped - 16 lines]

How do I do the date range in the criteria to capture through begin and end
date?

 

[John Spencer]

The best option is probably the following.

SELECT tblEmployeeSupervisorJctn.chrSupervisorID
, tblEmployeeSupervisorJctn.chrEmployeeUserID
, tblEmployeeSupervisorJctn.dtmBeginDate
, tblEmployeeSupervisorJctn.dtmEndDate
FROM tblEmployeeSupervisorJctn
WHERE chrSupervisorID=[Enter Supervisor ID]
AND Nz(dtmEndDate,Date()) >= CDate([Start of Period])
AND dtmBeginDate <= CDate([End of Period])


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

That is almost perfect!! The only thing it is not capturing is if there is
no end date. No end date would mean that the employee still reports to that
supervisor.

If I put in 01/01/2008 for Start of Period
and then 04/01/2008 for End of Period

I have the following entries that are not being returned, but need to be:
chrSupervisorID chrEmployeeUserID dtmBeginDate
dtmEndDate
JUL4125 HFT1255 01/26/2007
JUL4125 LOS6334 03/24/2008

THANK YOU SO MUCH FOR HELPING ME!

SELECT tblEmployeeSupervisorJctn.chrSupervisorID
, tblEmployeeSupervisorJctn.chrEmployeeUserID
, tblEmployeeSupervisorJctn.dtmBeginDate
, tblEmployeeSupervisorJctn.dtmEndDate
FROM tblEmployeeSupervisorJctn
WHERE chrSupervisorID=[Enter Supervisor ID]
AND dtmEndDate >= CDate([Start of Period])
AND dtmBeginDate <= CDate([End of Period])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query from tblEmployeeSupervisorJctn. In this table there are 4
fields: chrSupervisorID, chrEmployeeUserID, dtmBeginDate, and dtmEndDate.

[quoted text clipped - 16 lines]

How do I do the date range in the criteria to capture through begin and end
date?