Query has "Too Few Parameters - Expected 6"

[Paul H]

Please take a look at this SQL for me. I only count 5 parameters which I believe I've declared.

PARAMETERS [Forms]![frmReports]![cboOrganization] Text ( 255 ), [Forms]![frmReports]![cboContactID] Text ( 255 ), [Forms]![frmReports]![txtSSN] Text ( 255 ), [Forms]![frmReports]![txtFromDate] DateTime, [Forms]![frmReports]![txtToDate] DateTime;
SELECT Contact.OrgNum, Organization.Sort, Contact.ContactID, Organization.Organization_Name, Contact.Sal, Contact.ContactFname1, Contact.ContactLname1, Contact.Address1, Contact.Address2, Contact.City, Contact.State, Contact.PostalCode, Applicant.SSN, Applicant.Appl_Fname, Applicant.Appl_MI, Applicant.Appl_Lname, Resubmits.FBI_NonClassifiable, qryReport_ID.Report_Title
FROM qryReport_ID, (Organization INNER JOIN Contact ON Organization.OrgNum=Contact.OrgNum) INNER JOIN (Applicant INNER JOIN Resubmits ON (Applicant.DtPaymtRecvd=Resubmits.DtPaymtRecvd) AND (Applicant.SSN=Resubmits.SSN) AND (Applicant.ContactID=Resubmits.ContactID) AND (Applicant.OrgNum=Resubmits.OrgNum)) ON (Contact.OrgNum=Applicant.OrgNum) AND (Contact.ContactID=Applicant.ContactID)
WHERE (((Resubmits.DtRecvdFromFBI) Between [Forms]![frmReports]![txtFromDate] And [Forms]![frmReports]![txtToDate]))
GROUP BY Contact.OrgNum, Organization.Sort, Contact.ContactID, Organization.Organization_Name, Contact.Sal, Contact.ContactFname1, Contact.ContactLname1, Contact.Address1, Contact.Address2, Contact.City, Contact.State, Contact.PostalCode, Applicant.SSN, Applicant.Appl_Fname, Applicant.Appl_MI, Applicant.Appl_Lname, Resubmits.FBI_NonClassifiable, qryReport_ID.Report_Title
HAVING (((Contact.OrgNum) Like Forms!frmReports!cboOrganization) And ((Contact.ContactID) Like Forms!frmReports!cboContactID) And ((Applicant.SSN) Like Forms!frmReports!txtSSN) And ((Resubmits.FBI_NonClassifiable)=-1))
ORDER BY Organization.Sort, Contact.ContactID;

Here's a pic of my parameter window.





I'm trying to export to a CSV file. I have another problem with this, but I need to get passed this first.

[June7]

Does qryReport_ID have any filter parameters?

[Paul H]

Thank you. Of course it does. Having a bad day.

Thanks again. I'll be back with more.

[HiTechCoach]

Please take a look at this SQL for me. I only count 5 parameters which I believe I've declared.

PARAMETERS [Forms]![frmReports]![cboOrganization] Text ( 255 ), [Forms]![frmReports]![cboContactID] Text ( 255 ), [Forms]![frmReports]![txtSSN] Text ( 255 ), [Forms]![frmReports]![txtFromDate] DateTime, [Forms]![frmReports]![txtToDate] DateTime;
SELECT Contact.OrgNum, Organization.Sort, Contact.ContactID, Organization.Organization_Name, Contact.Sal, Contact.ContactFname1, Contact.ContactLname1, Contact.Address1, Contact.Address2, Contact.City, Contact.State, Contact.PostalCode, Applicant.SSN, Applicant.Appl_Fname, Applicant.Appl_MI, Applicant.Appl_Lname, Resubmits.FBI_NonClassifiable, qryReport_ID.Report_Title
FROM qryReport_ID, (Organization INNER JOIN Contact ON Organization.OrgNum=Contact.OrgNum) INNER JOIN (Applicant INNER JOIN Resubmits ON (Applicant.DtPaymtRecvd=Resubmits.DtPaymtRecvd) AND (Applicant.SSN=Resubmits.SSN) AND (Applicant.ContactID=Resubmits.ContactID) AND (Applicant.OrgNum=Resubmits.OrgNum)) ON (Contact.OrgNum=Applicant.OrgNum) AND (Contact.ContactID=Applicant.ContactID)
WHERE (((Resubmits.DtRecvdFromFBI) Between [Forms]![frmReports]![txtFromDate] And [Forms]![frmReports]![txtToDate]))
GROUP BY Contact.OrgNum, Organization.Sort, Contact.ContactID, Organization.Organization_Name, Contact.Sal, Contact.ContactFname1, Contact.ContactLname1, Contact.Address1, Contact.Address2, Contact.City, Contact.State, Contact.PostalCode, Applicant.SSN, Applicant.Appl_Fname, Applicant.Appl_MI, Applicant.Appl_Lname, Resubmits.FBI_NonClassifiable, qryReport_ID.Report_Title
HAVING (((Contact.OrgNum) Like Forms!frmReports!cboOrganization) And ((Contact.ContactID) Like Forms!frmReports!cboContactID) And ((Applicant.SSN) Like Forms!frmReports!txtSSN) And ((Resubmits.FBI_NonClassifiable)=-1))
ORDER BY Organization.Sort, Contact.ContactID;

Here's a pic of my parameter window.





I'm trying to export to a CSV file. I have another problem with this, but I need to get passed this first.

Looking at your SQL you have some form references that do not match any parameters

Forms!frmReports!cboOrganization
Forms!frmReports!cboContactID
Forms!frmReports!txtSSN

These do match:

[Forms]![frmReports]![txtFromDate]
[Forms]![frmReports]![txtToDate]

They have to be typed/spelled identical to match. This include the square brackets.


I normally never export a query. I first append the data to a table then export the table. I find this works better.

Also, how are you executing the SQL?

[Paul H]

Coach,

I clearly pasted the wrong SQL into the code box, sorry. Not sure how that happpened. There are definitely 5 parameters, excluding the one in qryReport_ID, which I have now removed. As a matter of fact none of my parameter queries will export to text. I think these error messages are leading me down the wrong path. Oddly there is no problem exportng to Excel or .rtf formats. In VBA it boils down to the OutputTo method or the TransferText method. I think your last suggestion is the one I need to go with, exporting from a table which seems to work fine. I was trying to avoid building too many additional objects. I have hopes of building a self sustaining application that can be managed without me. I think that's wishful thinking, but I can hope.

[HiTechCoach]

Coach,

I clearly pasted the wrong SQL into the code box, sorry. Not sure how that happpened. There are definitely 5 parameters, excluding the one in qryReport_ID, which I have now removed. As a matter of fact none of my parameter queries will export to text. I think these error messages are leading me down the wrong path. Oddly there is no problem exportng to Excel or .rtf formats. In VBA it boils down to the OutputTo method or the TransferText method. I think your last suggestion is the one I need to go with, exporting from a table which seems to work fine. I was trying to avoid building too many additional objects. I have hopes of building a self sustaining application that can be managed without me. I think that's wishful thinking, but I can hope.

I have many self sustaining application. It is definitely possible.

I have many client that have been running applications I created for 10+ years that never have an issue. I usually call then once a year just to check in.

[ItsMe]

Are you using DAO to open the parameterized query object?

[HiTechCoach]

I was trying to avoid building too many additional objects.

IMHO, It is only additional if not really needed.

A wise programmer once told me: Shortcuts often lead to long delays.


I should have mentioned:

I use temp tables a lot. What I learn many years ago was to put them in a separate back end. I build a scratch/work database with code in the same folder as the front end. I have heard this referred to as a "side end". When the front end closes it deletes the temp database along with all the temp tables.

Temporary tables are even available in Microsoft SQL Server. See: http://www.sqlteam.com/article/temporary-tables

[Paul H]

Are you using DAO to open the parameterized query object?

No I'm not. ...

[Paul H]

IMHO, It is only additional if not really needed.

After I leave here (working as a contractor) the user, who has limited skills, will take over. She will have to add queries and tables as the need arises. The fewer steps I put in place, the better for her and the less likely she will stumble. I feel I owe her that much, but a certain amount of technical support and expertise may be necessary. I'm just not sure she'll get it.