display records for so many days

[Chad]

Hello I have a query that collects records from a field named "PeriodRatedTo"
and its criteria is <=Date(). How could I display records starting 30 days
prior to the "PeriodRatedTo" date and 30 days after the "PeriodRatedTo" date?
Thanks!

SELECT tbl_PerformanceReviewMain.EmpID, tblEmployees.EmployeeName,
tblTypeofReview.TypeOfReview, tbl_PerformanceReviewMain.PeriodRatedTo,
tbl_PerformanceReviewMain.Complete, tblEmployees.Status
FROM tblTypeofReview INNER JOIN (tblEmployees INNER JOIN
tbl_PerformanceReviewMain ON tblEmployees.EmployeeID =
tbl_PerformanceReviewMain.EmployeeName) ON tblTypeofReview.TypeOfReviewID =
tbl_PerformanceReviewMain.TypeofReview, tblTypeofReview AS tblTypeofReview_1
INNER JOIN (tblEmployees AS tblEmployees_1 INNER JOIN
tbl_PerformanceReviewMain AS tbl_PerformanceReviewMain_1 ON
tblEmployees_1.EmployeeID = tbl_PerformanceReviewMain_1.EmployeeName) ON
tblTypeofReview_1.TypeOfReviewID = tbl_PerformanceReviewMain_1.TypeofReview
WHERE (((tbl_PerformanceReviewMain.PeriodRatedTo)<=Date()) AND
((tbl_PerformanceReviewMain.Complete)=0) AND ((tblEmployees.Status)=False));

 

[Steve Schapel]

Chad,

I can't quite understand what you mean. If I assume what you really
want is records where the PeriodRatedTo date is after 30 days before the
current date, and before 30 days after the current date, then you can
use criteria like:

.... PeriodRatedTo Between Date()-30 And Date()+30

 

[John Spencer]

Try changing the query to the following.


SELECT tbl_PerformanceReviewMain.EmpID, tblEmployees.EmployeeName,
tblTypeofReview.TypeOfReview, tbl_PerformanceReviewMain.PeriodRatedTo,
tbl_PerformanceReviewMain.Complete, tblEmployees.Status
FROM tblTypeofReview INNER JOIN (tblEmployees INNER JOIN
tbl_PerformanceReviewMain ON tblEmployees.EmployeeID =
tbl_PerformanceReviewMain.EmployeeName) ON tblTypeofReview.TypeOfReviewID =
tbl_PerformanceReviewMain.TypeofReview, tblTypeofReview AS
tblTypeofReview_1
INNER JOIN (tblEmployees AS tblEmployees_1 INNER JOIN
tbl_PerformanceReviewMain AS tbl_PerformanceReviewMain_1 ON
tblEmployees_1.EmployeeID = tbl_PerformanceReviewMain_1.EmployeeName) ON
tblTypeofReview_1.TypeOfReviewID = tbl_PerformanceReviewMain_1.TypeofReview

WHERE tbl_PerformanceReviewMain.PeriodRatedTo>=DateAdd("d",-30,Date())
AND tbl_PerformanceReviewMain.PeriodRatedTo<=DateAdd("d",30,Date())
AND tbl_PerformanceReviewMain.Complete=0
AND tblEmployees.Status=False

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

 

[Chad]

Im sorry I might not have explained it well enough! I want it to continue to
show the record for every day starting 30 days before untill 30 days after.
lets say the "PeriodRatedTo" was 12/25/2007 then thi would be showing there
is a record and continue to show untill it eather goes 30 days past the
"PeriodRatedTo" date. I hope im explaining this better....Thanks!

 

[Chad]

Sorry John, after messing with adding the >=DateAdd("d",-30,Date()) And
<=DateAdd("d",30,Date()) to the"PeriodRatedTo" field it is desplaying the
record every day for 30 days and every dayd for 30 days after. Thank you! I
have been trying to figure out something for days....