Hi All,
I have a a bit sloppy, but functional query below that works perfectly fine in Access, but when I try to open the query in vba using [openrecordset] I get a "3061 too few parameters; expected 1" error. Originally, I had the SQL hardcoded in VBA as a string, but to prevent errors, I made it a saved query which is still failing. Any ideas here? Would love to post the DB, but it will be way too time consuming to strip. Will try to provide as much additional information as needed though.
Thanks
VBA:
Code:
Dim rstQueryEmailNames As DAO.Recordset
Set rstQueryEmailNames = CurrentDb.OpenRecordset("qryEmailNamesFilteredTeam")
SQL:
Code:
SELECT DISTINCT [myHelperQry].[USER ID], [myHelperQry].PersonnelID, [myHelperQry].FirstName, [myHelperQry].LastName
FROM (SELECT DISTINCT tblPersonnel.PersonnelID, Team, tblPersonnel.USER_ID AS [USER ID], tblPersonnel.Last_Name AS LastName, tblPersonnel.First_Name AS FirstName, IIf([tblPersonnelTrainingExemptions]![PersonnelID]=[qryAllDistinctTrainings]![PersonnelID] And [tblPersonnelTrainingExemptions]![TrainingID]=[qryAllDistinctTrainings]![TrainingID],'Exempt',IIf([Date Last Completed] Is Null,'Pending',IIf(IsNumeric([Days Remaining])=True And [Days Remaining]<0,'Expired',IIf(IsNumeric([Days Remaining])=True And [Days Remaining]>=0 And [Days Remaining]<=75,'Expires Soon','Completed')))) AS Status, qryAllPersonnelTrainingReportHelper.[Date Last Completed], IIf([Date Last Completed] Is Null,0,IIf([LifeExpectancyDays] Is Null,999,DateDiff('d',Date(),[Date Last Completed]+[LifeExpectancyDays]))) AS [Days Remaining], tblPersonnel.Active FROM ((tblPersonnel LEFT JOIN qryPersonnelOrganization ON tblPersonnel.PersonnelID = qryPersonnelOrganization.PersonnelID) INNER JOIN ((qryAllDistinctTrainings LEFT JOIN qryAllPersonnelTrainingReportHelper ON (qryAllDistinctTrainings.PersonnelID = qryAllPersonnelTrainingReportHelper.PersonnelID) AND (qryAllDistinctTrainings.TrainingID = qryAllPersonnelTrainingReportHelper.TrainingID)) INNER JOIN tblTrainingList ON qryAllDistinctTrainings.TrainingID = tblTrainingList.TrainingID) ON tblPersonnel.PersonnelID = qryAllDistinctTrainings.PersonnelID) LEFT JOIN tblPersonnelTrainingExemptions ON (qryAllDistinctTrainings.PersonnelID = tblPersonnelTrainingExemptions.PersonnelID) AND (qryAllDistinctTrainings.TrainingID = tblPersonnelTrainingExemptions.TrainingID)) AS [myHelperQry]
WHERE ((([myHelperQry].Status)<>'Completed') AND (([myHelperQry].Team)=[Forms]![frmMain]![txtTeamFilter]));