With Export File Wizard, "Two Few Parameters. Expected 2."

[HoganGroup]

I need to create an export file for the following brutal query to upload into a state database (go figure it's a government report!) All was good in my world until I tried to export. Manually or TransferText, didn't matter, it just hated me. The above error stopped me cold in my tracks. Searching about, no one else seems to know what the heck this stupid error is. Anybody? Bueller?

SELECT DISTINCT Format([Forms]![frmReports]![txtFrom],"mm/yyyy") AS [Month], tblSettings.CDEVendorNo AS Vendor, tblFamilies.FamilyID AS FICN, tblFamilies.SSN, tblFamilies.ParentALast, tblFamilies.ParentAFirst, "" AS MI, tblFamilies.ParentAZip, "N" AS CW, "N" AS 75SMI, tblEligibility.Need, tblFamilies.FIPS, Format([qryFirstStart_Family].[FirstStart],"mm/dd/yyyy") AS FamilyStart, tblKids.KidLast, tblKids.KidFirst, "" AS MI2, Format([DOB],"mm/dd/yyyy") AS DB, IIf([tblKids.ExceptionalNeeds]=Yes,"Y","N") AS IEP, Format([qryFirstStartLastEnd_Kid].[FirstStart],"mm/dd/yyyy") AS KidStart, tblSettings.TaxIDNo AS EIN, IIf([ProviderType]="FCCN","02","04") AS Type, "CCTR" AS Program1, "" AS Program2, "" AS Program3, Format([qryFirstStart_Family].[FirstStart],"mm/dd/yyyy") AS FamilyStart2
FROM tblSettings, tblProviders INNER JOIN (((((tblKids INNER JOIN tblFamilies ON tblKids.FamilyID = tblFamilies.FamilyID) INNER JOIN tblEligibility ON tblFamilies.FamilyID = tblEligibility.FamilyID) INNER JOIN (tblSchedules INNER JOIN qryToFrom ON tblSchedules.SchID = qryToFrom.SchID) ON tblKids.KidID = tblSchedules.KidID) INNER JOIN qryFirstStart_Family ON tblFamilies.FamilyID = qryFirstStart_Family.FamilyID) INNER JOIN qryFirstStartLastEnd_Kid ON tblKids.KidID = qryFirstStartLastEnd_Kid.KidID) ON tblProviders.ProviderID = tblSchedules.ProviderID;

[nico5038]

The error message indicates that two of your fields aren't found by Access in the table.
One way to find the fields is to use a copy of the query and remove the fields one by one to detect when the message stops appearing....

Nic;o)

[HoganGroup]

Thanks for the strategy. I used it to identified the offending elements.

Format([Forms]![frmReports]![txtFrom],"mm/yyyy") AS [Month]

and

FROM ... INNER JOIN qryToFrom ...

Their common theme is that they get parameters from an open form. This appears to be the issue. The problem is that need those fields, it sets the date range for the report. Is there a better way to set it up that I haven't done?

Thanks again,
Bradley

[HoganGroup]

Success!

Beat that nasty parameter issue by making it a make-table query and then exporting the resulting table instead. I don't know if it was the best way, but it worked!

Thanks again,
Bradley

[nico5038]

The "basic" solution would be to trigger the export from the frmReport and have the fields filled as required.
The other option is to change "[Forms]![frmReports]![txtFrom],"mm/yyyy"" into:
[Enter Month/Year(mm/yyyy): ] and Access will ask to enter the value.

Nic;o)