need query to show additional records

[CEV]

I have a query that shows records of employees in every position for the
fiscal year 7/1/2006 through 6/30/2007. If more than one employee has been
in that position during the year, it would show both. What I did was request
the data by asking for start dates during the fiscal year. However this will
not show employees or positions for the employees that started before
7/1/2006 but were still in the position as of that date. I hope I have
explained my issue well enough and that someone can help me out.

My Query is as follows:

PositionNumber from tblEmployeePosition
EmployeeNumber from tblEmployeePosition
LastName from tblEmployees
FirstName from tblEmployees
DateStarted from tblEmployeePosition
DateEnded from tblEmployeePosition

DateStarted has a criteria of Between [Forms]![frmReports]![txtBeginDate]
And [Forms]![frmReports]![txtEndDate]

Thanks,

Chad

 

[Carl Rapson]

I have a query that shows records of employees in every position for the
fiscal year 7/1/2006 through 6/30/2007. If more than one employee has been
in that position during the year, it would show both. What I did was
request the data by asking for start dates during the fiscal year. However
this will not show employees or positions for the employees that started
before 7/1/2006 but were still in the position as of that date. I hope I
have explained my issue well enough and that someone can help me out.

My Query is as follows:

PositionNumber from tblEmployeePosition
EmployeeNumber from tblEmployeePosition
LastName from tblEmployees
FirstName from tblEmployees
DateStarted from tblEmployeePosition
DateEnded from tblEmployeePosition

DateStarted has a criteria of Between [Forms]![frmReports]![txtBeginDate]
And [Forms]![frmReports]![txtEndDate]

Thanks,

Chad

Try using just txtEndDate:

DateStarted < [Forms]![frmReports]![txtEndDate]


Carl Rapson

 

[John Spencer]

WHERE DateStarted <= [Forms]![frmReports]![txtEndDate] and
(DateEnded >= [Forms]![frmReports]![txtBeginDate] Or DateEnded is Null)

To be in a period the Date Start must be on or before the End of the
period and the Date End must be on or after the Beginning of the period
Or it must be blank (null).

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

 

[CEV]

Thanks John, that did exactly what I wanted it to.

Thanks,

Chad

WHERE DateStarted <= [Forms]![frmReports]![txtEndDate] and
(DateEnded >= [Forms]![frmReports]![txtBeginDate] Or DateEnded is Null)

To be in a period the Date Start must be on or before the End of the
period and the Date End must be on or after the Beginning of the period Or
it must be blank (null).

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

I have a query that shows records of employees in every position for the
fiscal year 7/1/2006 through 6/30/2007. If more than one employee has
been in that position during the year, it would show both. What I did was
request the data by asking for start dates during the fiscal year.
However this will not show employees or positions for the employees that
started before 7/1/2006 but were still in the position as of that date. I
hope I have explained my issue well enough and that someone can help me
out.

My Query is as follows:

PositionNumber from tblEmployeePosition
EmployeeNumber from tblEmployeePosition
LastName from tblEmployees
FirstName from tblEmployees
DateStarted from tblEmployeePosition
DateEnded from tblEmployeePosition

DateStarted has a criteria of Between [Forms]![frmReports]![txtBeginDate]
And [Forms]![frmReports]![txtEndDate]

Thanks,

Chad