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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hamed Nasr
Send something to reproduce the issue.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).Na
Last, you could modify the SQL on the fly to actually build the SQL string with the correct values and then execute it.
JimD.
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).Na
Last, you could modify the SQL on the fly to actually build the SQL string with the correct values and then execute it.
JimD.
ASKER
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
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
ASKER
And i forgot to add - i dont want to 'push' the pivottable from Access
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.
JimD.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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