• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1084
  • Last Modified:

too few parameters. expected 2

Hi

I have queries setup in access that are using query by form i.e parameters passed via text boxes - the queries work fine when running within access. I am trying to connect an excel workbook and its pivottable to this query but during the wizard i get the error~: "too few parameters. expected 2" is this a limitation or can i somehow get around this.

Thanks
0
Jimmy_inc
Asked:
Jimmy_inc
3 Solutions
 
david251Commented:
The way that I have done these queries in the past is to use Microsoft query to do the parameterization.  You can find a how to here:
http://office.microsoft.com/en-us/excel/HP051995481033.aspx
HTH,
-david251
0
 
hnasrCommented:
Send something to reproduce the issue.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

<<"too few parameters. expected 2">>
 it's not a limitation.  When you open a query in code, Access leaves everything up to you<g>. That includes resolving any references. Fortunately, there is an easy way around that:

Dim db As Database
Dim qdef As QueryDef
Dim rs As Recordset


Set db = CurrentDb()
Set qdef = db.QueryDefs(source)
qdef.Parameters(0) = Eval(qdef.Parameters(0).Name)
Set rs = qdef.OpenRecordset()

Since the parameter name is a reference to the form and control, using Eval() gets the value for you. If you had multiple parameters, then you would just loop on the parameters collection doing a Eval() for each.

JimD.
 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Here is an example of a loop:
Dim db As Database
Dim qdef As QueryDef
Dim prm as Parameter
Dim rs As Recordset


Set db = CurrentDb()
Set qdef = db.QueryDefs(source)
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdef.OpenRecordset()  

You could also set the paramertes by name:

qdef.Parameters("<name>") = Eval(qdef.Parameters(0).Name)  
Last, you could modify the SQL on the fly to actually build the SQL string with the correct values and then execute it.
JimD.
 
0
 
Jimmy_incAuthor Commented:
Thanks David - when i get a chance i will have a quick look.
Thanks Jim but how would excel pivottables work with this code?

What if i were to run code in access then code which opens an excel template (with pivottable) which connects back to the access file ? (- , i just prefer working in access - it seems more stable than Excel
0
 
Jimmy_incAuthor Commented:
And i forgot to add - i dont want to 'push' the pivottable from Access
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Sorry, I didn't notice we were in the Excel zone and the way the question was worded, I thought this was in a Access DB.
JimD.
0
 
Rory ArchibaldCommented:
If you want to stay in Access (it's not more stable than Excel, BTW!), then the simplest thing would be to use a make table query, then base the pivot table off the new table.
0
 
Jimmy_incAuthor Commented:
Good point Jim (-:
Thanks Rorya - sometimes the simplest way of doing this is best - Next time I will ask the experts up-front first!
And thanks David for also helping to make up my mind
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now