Me.Recordsource="QueryName"
That won't pass parameters to the query though. I need to do that through VBA. The user has the option of entering or not entering dates. If no dates are entered, i.e., the controls txtStartDate and txtEndDate are left empty, the results will not be limited by date. If the user enters txtStartDate, but no end date, the SQL will be Beween #2017-01-01# AND Now(). If the user enters both, then SQL will be Between #2017-01-01# AND #2018-12-31#. The user is also selecting a course to search by from a combobox.
Code:
Dim qdf as DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryStatsByCourseGraph3")
qdf![CourseID] = Me.OpenArgs
qdf![StartDate] = "2018-01-01"
qdf![EndDate] = Now()
Set qdf = Nothing
This works, but qryStatsByCourseGraph3 looks like this:
Code:
SELECT qryOfficesInDistrict.OfficeName, Nz([CountComplete],0) AS TotalActiveDone, qryEmployeeCount04.Total, qryEmployeeCount04.TotalActive, Sum([Total]-[TotalActive]) AS TotalInactive, Sum([TotalActive]-[TotalActiveDone]) AS TotalActiveNotDone
FROM (qryOfficesInDistrict LEFT JOIN qryCount02 ON qryOfficesInDistrict.OfficeName = qryCount02.OfficeName) INNER JOIN qryEmployeeCount04 ON qryOfficesInDistrict.OfficeName = qryEmployeeCount04.OfficeName
GROUP BY qryOfficesInDistrict.OfficeName, Nz([CountComplete],0), qryEmployeeCount04.Total, qryEmployeeCount04.TotalActive;
To make things more complicated, the parameters go into another query not even referenced in the one above:
Code:
SELECT qryUnionInDistrictLocation.OfficeName, tblIndividualLearning.CatalogueID, Max(tblIndividualLearning.DateCompleted) AS MaxOfDateCompleted, tblIndividualLearning.EmpID, tblEmployee.LastName, tblEmployee.SubstantivePosition, Max(tblIndividualLearning.DateCompleted) AS MaxOfDateCompleted1
FROM (qryUnionInDistrictLocation INNER JOIN tblEmployee ON qryUnionInDistrictLocation.EmpID = tblEmployee.EmpID) INNER JOIN tblIndividualLearning ON (tblEmployee.EmpID = tblIndividualLearning.EmpID) AND (tblEmployee.EmpID = tblIndividualLearning.EmpID)
GROUP BY qryUnionInDistrictLocation.OfficeName, tblIndividualLearning.CatalogueID, tblIndividualLearning.EmpID, tblEmployee.LastName, tblEmployee.SubstantivePosition, qryUnionInDistrictLocation.District
HAVING (((tblIndividualLearning.CatalogueID)=[CourseID]) AND ((Max(tblIndividualLearning.DateCompleted)) Between [StartDate] And [EndDate]) AND ((qryUnionInDistrictLocation.District)=FindDistrict()));
So basically, I need to get Course ID, Start Date and End Date to this last query through VBA. Using qdf does that, but now I need to use those results as the report record source.
If you made it here, thanks for sticking with me!!!