Starting And Stopping Within A Selected date Range

[Tom]

I have a TblProject with fields ProjectID, ProjectName, ProjectNum, etc. and a
TblProjectWorkDays with the fields ProjectWorkDayID, ProjectID, ProjectWorkDate,
WorkDesc, etc. I need to create a query that returns all the projects started
and completed in a selected date range. Started means earliest ProjectWorkDate
is after the selected StartDate and completed means latest ProjectWorkDate is
before the selected EndDate. I know how to build a popup form with two textboxes
to enter the Start and End dates and I know how to set up the criteria. I need
help on how to return the earliest date and the latest date for each project in
the query. I tried using the DMin function for the earliest date and DMax
function for the latest date and setting the criteria to greater than or equal
to StartDate for the DMin field and less than or equal to for the DMax field but
the query doesn't return all the projects in the daye range when I manually
check the output. Apparently, the query struggles with calculating DMax and DMin
and only returning projects in the date range at the same time. Another thing is
that the query is very slow running the DMin and DMax functions for every
record. Is there some way of writing the query so it returns all projects where
all the ProjectWorkDates for the project are within a selected date range?

Thanks for all help!

Tom

 

[Howard Brody]

Try doing it in 2 queries.

For the first (qryProjectDates):

SELECT ProjectID, Min(ProjectWorkDate) AS StartDate, MAX
(ProjectWorkDate) AS EndDate
FROM tblProject
GROUP BY ProjectID

For the second:

SELECT ProjectID
FROM qryProjectDates
WHERE StartDate > = [Enter Start Date] AND EndDate < =
[Enter End Date]

I think this should give you the results you need.

Hope this helps!

Howard Brody