Too few parameters...

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

BobRoyAce

I have a query that, when I attempt to execute using code
(e.g. Set rs = CurrentDB.OpenRecordset(...)), I get a
message indicating "Too few parameters. Expected 0." If
zero are expected, then how is it too few!? What does
this error mean. Queries involved are shown below. NOTE:
ARM is one of values returned for Team in
NewReportQuery1Year.

------- Query -------
SELECT NewReportQuery1Year.FunctionalCategoryDesc,
NewReportQuery1Year.ARM
FROM NewReportQuery1Year
ORDER BY NewReportQuery1Year.FunctionalCategoryDesc

------- NewReportQuery1Year ------
PARAMETERS [Forms]![Reporting]![FiscalYear] IEEEDouble;
TRANSFORM Sum([Raw Downtime Data With Detail].[Time
(min)]) AS LostTime
SELECT FunctionalCategories.FunctionalCategoryDesc
FROM [Raw Downtime Data With Detail] INNER JOIN
(FunctionalCategories INNER JOIN Categories ON
FunctionalCategories.FunctionalCategoryCd =
Categories.FunctionalCategoryCd) ON [Raw Downtime Data
With Detail].[Category Type] = Categories.Category
WHERE [Raw Downtime Data With Detail].[Fiscal Year] =
[Forms]![Reporting]![FiscalYear]
GROUP BY FunctionalCategories.FunctionalCategoryDesc
PIVOT [Raw Downtime Data With Detail].Team;
 
Hi Bob,

Joe Fallon said it well:

***quote***
Access can figure out what the parameter is
when the query is run in the grid. Then Access
will inform the Jet Engine what it is.

However, when the same query is run in code,
you must tell the Jet engine what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError

--
Joe Fallon

***unquote***

Of course you need to set reference to DAO library.
 
Back
Top