Odd Too few parameters issue

[tighe]

all thanks for all the help past anf future.

my current issue is that i have a query and get the titled error. now the
odd part is i have a backup copy of my FE (both use the same BE) that works
with no issue. i copied all objects the query uses except tables from the
backup to the current and get the same results. the query does use 2
crosstab queries in it and i know that when they are removed the errors go
away, but then again so does the information i need from them. i also
already tried creating crosstabs that do not use parameters but then i get an
error that Access does recognize a filter. i 'm also tried compacting.

not sure what other information to give excpet AC2007/XP.

 

[Duane Hookom]

This is difficult to troubleshoot without seeing your data, the SQL views of
your queries, or how a query would result in the error that typically is
raised in code.

Do you understand how to set the Parameter datatypes of criteria in
crosstabs? Also, are you using the Column Headings property of your crosstab?

 

[tighe]

Duane,

Thanks for the response. I am pretty sure i'm doing the cross tab items
correctly they work by themselves just not inconjuction with with the new
query.

Crosstab 1:
PARAMETERS [Forms].[001_Start].[List9] Short;
TRANSFORM Sum(Firm_Products_qry_pre.[%]) AS [SumOf%]
SELECT Firm_Products_qry_pre.Firm_id
FROM Firm_Criteria_Selection LEFT JOIN Firm_Products_qry_pre ON
Firm_Criteria_Selection.wsp_con_number = Firm_Products_qry_pre.wsp_con_number
GROUP BY Firm_Products_qry_pre.Firm_id
ORDER BY Firm_Products_qry_pre.Firm_id DESC
PIVOT [Initials] & "%";

Crosstab 2:
PARAMETERS [Forms].[001_Start].[List9] Short;
TRANSFORM Sum(Firm_Products_qry_pre.Does) AS SumOfDoes
SELECT Firm_Products_qry_pre.Firm_id
FROM Firm_Criteria_Selection LEFT JOIN Firm_Products_qry_pre ON
Firm_Criteria_Selection.wsp_con_number = Firm_Products_qry_pre.wsp_con_number
GROUP BY Firm_Products_qry_pre.Firm_id
ORDER BY Firm_Products_qry_pre.Firm_id DESC
PIVOT Firm_Criteria_Selection.Initials;

Query that uses the crosstabs but returns the error, as i mentioned i have
this working in an old FE and even Deleted from new FE and replaced all items
used:
SELECT [1_Firm_Info].Firm_ID, [1_Firm_Info].Firm_Name,
[1_Firm_Info].Address1, [1_Firm_Info].Address2, [1_Firm_Info].Address3,
[1_Firm_Info].City, [1_Firm_Info].State, [1_Firm_Info].Zip,
[2_Empl_Info]![Empl_First] & " " & [2_Empl_Info]![Empl_Last] AS CE_Empl,
[2_Empl_Info].CEDP, [Firm_CCO]![Empl_First] & " " & [Firm_CCO]![Empl_Last] AS
CCO, Firm_CCO.Bio, [Firm_CEO]![Empl_First] & " " & [Firm_CEO]![Empl_Last] AS
CEO, Firm_CEO.Bio, [Firm_CFO]![Empl_First] & " " & [Firm_CFO]![Empl_Last] AS
CFO, Firm_CFO.Bio, [Firm_Options]![Empl_First] & " " &
[Firm_Options]![Empl_Last] AS Options, Firm_Options.Bio,
[Firm_Muni]![Empl_First] & " " & [Firm_Muni]![Empl_Last] AS Muni,
Firm_Muni.Bio, [1_Firm_Info].minimum_net_capital,
Firm_Exec_TotalNums.CountOfEmpl_ID, Firm_Rep_TotalNums.CountOfEmpl_ID,
[1_Firm_AcctCust_BD].[Cash#], [1_Firm_AcctCust_BD].[Margin#],
[1_Firm_AcctCust_BD].[IA#], [1_Firm_AcctCust_BD].[Discretionary#],
[1_Firm_AcctCust_BD].[Option#], [1_Firm_AcctCust_BD].[Other#],
[1_Firm_AcctCust_BD].[Retail%], [1_Firm_AcctCust_BD].[HighNetWorth%],
[1_Firm_AcctCust_BD].[Institutional%], [1_Firm_AcctCust_BD].[OtherCust%],
[1_Firm_AcctCust_BD].[20-35], [1_Firm_AcctCust_BD].[36-55],
[1_Firm_AcctCust_BD].[56-65], [1_Firm_AcctCust_BD].[>66],
[1_Firm_AcctCust_BD].Age_Info, [1_Firm_Products].SaleMark_Info,
[1_Firm_Products].OTHDesc,
DLookUp("[note]","[Firm_Product_List_Full]","[Firm_Product_List_Full]![wsp_con_number]=52
And [Firm_Product_List_Full]![Firm_id]=[Forms]![001_Start]![List9]") AS
OtherCustDescr, [Firm_ER]![Empl_First] & " " & [Firm_ER]![Empl_Last] AS ER,
Firm_ER.Bio, IIf([1_Firm_Services]![ACM] Is Null,"",[1_Firm_Services]![ACM])
AS ACM, [1_Firm_Info].CPExempt, [Firm_Product_List_Full_%].*,
Firm_Product_List_Full_Crosstab.TRA, Firm_Product_List_Full_Crosstab.IDM,
Firm_Product_List_Full_Crosstab.[OTH MA], Firm_Product_List_Full_Crosstab.RPT
FROM (((((((((((((1_Firm_Info LEFT JOIN Firm_CEO ON
[1_Firm_Info].Firm_ID=Firm_CEO.Firm_ID) LEFT JOIN Firm_CCO ON
[1_Firm_Info].Firm_ID=Firm_CCO.Firm_ID) LEFT JOIN Firm_CFO ON
[1_Firm_Info].Firm_ID=Firm_CFO.Firm_ID) LEFT JOIN Firm_Muni ON
[1_Firm_Info].Firm_ID=Firm_Muni.Firm_ID) LEFT JOIN Firm_Options ON
[1_Firm_Info].Firm_ID=Firm_Options.Firm_ID) LEFT JOIN Firm_Rep_TotalNums ON
[1_Firm_Info].Firm_ID=Firm_Rep_TotalNums.Firm_ID) LEFT JOIN
Firm_Exec_TotalNums ON [1_Firm_Info].Firm_ID=Firm_Exec_TotalNums.Firm_ID)
LEFT JOIN 1_Firm_Products ON [1_Firm_Info].Firm_ID=[1_Firm_Products].Firm_ID)
LEFT JOIN 1_Firm_AcctCust_BD ON
[1_Firm_Info].Firm_ID=[1_Firm_AcctCust_BD].Firm_ID) LEFT JOIN 2_Empl_Info ON
[1_Firm_Info].Firm_ID=[2_Empl_Info].Firm_ID) LEFT JOIN Firm_ER ON
[1_Firm_Info].Firm_ID=Firm_ER.Firm_ID) LEFT JOIN 1_Firm_Services ON
[1_Firm_Info].Firm_ID=[1_Firm_Services].Firm_ID) LEFT JOIN
[Firm_Product_List_Full_%] ON
[1_Firm_Info].Firm_ID=[Firm_Product_List_Full_%].Firm_id) INNER JOIN
Firm_Product_List_Full_Crosstab ON
[1_Firm_Info].Firm_ID=Firm_Product_List_Full_Crosstab.Firm_id
WHERE ((([1_Firm_Info].Firm_ID)=Forms![001_Start]!List9) And
(([2_Empl_Info].CEDP)=-1));

 

[Roger Carlson]

Just to expand on Duane's post: Ordinarily, you don't have to define the
parameter datatype in the PARAMETERS clause for a normal Select Query. But
you DO have to define it in order to use a parameter in a crosstab query.
This blog post:
http://rogersaccessblog.blogspot.com/2009/07/select-queries-part-4-parameters-top.html
(near the bottom) discusses the PARAMETERS clause and how to use in in a
crosstab.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[tighe]

Roger,

I think maybe i was misreading both duane and your comments, it looks like
the solution was removing parameters from the Crosstabs but adding the
parameter to the select query.