By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Parameters and Append Queries

P: n/a
I'm trying to get the following simplified test query to work so that I can
apply it to a more complex final query.
I get a 'Too few parameters' error when I try to run the following append
query:

Dim db as DAO.Database, qd as DAO.QueryDef, prm as DAO.Parameter
Set db = Currentdb
Set qd = db.QueryDefs("aaTest")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next
db.Execute "aaTest", dbFailOnError

The SQL for aaTest is:

INSERT INTO tArrearsGrouped ( LAN, Amount)
SELECT tIncome.LAN, Sum(tIncome.AmtRec) AS SumOfAmtRec
FROM tIncome
GROUP BY tIncome.LAN
HAVING (((tIncome.LAN)=[Forms]![fTenantDetails]![LAN]));

fTenantDetails is open when the query is run, and the correct value is found
in 'For each...'.
For the final query, the parameter is buried in a right join of a query
within a query, so I cannot apply a simple 'WHERE' condition to the
db.Execute. ie the parameter needs to be applied at the level of that query.

If I convert AATest to a select query, I can open a recordset on it, so I
must be getting at least part of it right(I think).
Any ideas appreciated.

--
Bob Darlington
Brisbane
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi Bob
Neither of my parameter queries ever agreed to work taking parameter
value from a form. I have many of them and tried quite hard to use the
syntax like you do, but the only way I found is:
HAVING (((tIncome.LAN)=[Parameter Name])) in query and
prm.Value = [Forms]![fTenantDetails]![LAN] in code.
I also define parameters each on a separate line, like:
set myPar=qd![Parameter Name]
myPar=[Forms]![fTenantDetails]![LAN]
Hope it will help.
Galina

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message news:<40***********************@news.optusnet.com. au>...
I'm trying to get the following simplified test query to work so that I can
apply it to a more complex final query.
I get a 'Too few parameters' error when I try to run the following append
query:

Dim db as DAO.Database, qd as DAO.QueryDef, prm as DAO.Parameter
Set db = Currentdb
Set qd = db.QueryDefs("aaTest")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next
db.Execute "aaTest", dbFailOnError

The SQL for aaTest is:

INSERT INTO tArrearsGrouped ( LAN, Amount)
SELECT tIncome.LAN, Sum(tIncome.AmtRec) AS SumOfAmtRec
FROM tIncome
GROUP BY tIncome.LAN
HAVING (((tIncome.LAN)=[Forms]![fTenantDetails]![LAN]));

fTenantDetails is open when the query is run, and the correct value is found
in 'For each...'.
For the final query, the parameter is buried in a right join of a query
within a query, so I cannot apply a simple 'WHERE' condition to the
db.Execute. ie the parameter needs to be applied at the level of that query.

If I convert AATest to a select query, I can open a recordset on it, so I
must be getting at least part of it right(I think).
Any ideas appreciated.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.