Hi all,

Using A2007. I'm using an append query to load data into a temporary
table.
The query works fine when I run it from the query view. But when I
use Execute and reference it, or pass the SQL as a string, it brings
up 'Run-time error '3061' - Too few parameters. Expected 2.

I create the table using:

================================================== =========

Dim db As Database
Dim temp_table As TableDef
Set db = DBEngine(0)(0)
Set temp_table = db.CreateTableDef("TEMPtblReport1")

With temp_table
.Fields.Append .CreateField("GroupID", dbInteger)
.Fields.Append .CreateField("GroupName", dbText)
.Fields.Append .CreateField("CompanyID", dbDouble)
.Fields.Append .CreateField("CompanyName", dbText)
.Fields.Append .CreateField("ProductID", dbText)
.Fields.Append .CreateField("ProductName", dbText)
.Fields.Append .CreateField("ValueOfSale",
dbCurrency)
.Fields.Append .CreateField("Quantity", dbInteger)
.Fields.Append .CreateField("Discount",
dbCurrency)
.Fields.Append .CreateField("List Price",
dbCurrency)
.Fields.Append .CreateField("DateOfSale", dbDate)
End With

db.TableDefs.Append temp_table

================================================== =========

then try to append data to it using

================================================== ==========

INSERT INTO TEMPtblReport1
(GroupID, GroupName, CompanyID, CompanyName, ProductID, ProductName,
Quantity, ValueOfSale, Discount,[List Price], DateOfSale )
SELECT
qryRetroGroupCompanyProductMonth.GroupID,
qryRetroGroupCompanyProductMonth.GroupName,
qryRetroGroupCompanyProductMonth.CompanyID,
qryRetroGroupCompanyProductMonth.CompanyName,
qryRetroGroupCompanyProductMonth.ProductID,
qryRetroGroupCompanyProductMonth.ProductName,
qryRetroGroupCompanyProductMonth.Quantity,
qryRetroGroupCompanyProductMonth.ValueOfSale,
qryRetroGroupCompanyProductMonth.Discount,
qryRetroGroupCompanyProductMonth.[List Price],
qryRetroGroupCompanyProductMonth.DateOfSale

FROM qryRetroGroupCompanyProductMonth;

================================================

This works when running the query directly in query view, but gives
the error when using db.Execute with either a query name or the
query's SQL passed as a string.

I'm going round and round in circles. Can anyone help?

Thanks,

Franc.

On Dec 7, 4:24*pm, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com>
wrote:
The table creation code you provided was handy for making your table
quickly. Using another table in lieu of your query, I had no trouble
running your Append Query from either the QBE interface or from form
code in A97. I note that when grabbing SQL from the QBE interface to
put into form code, I start at the beginning of the bottom line, then
hit the Backspace key, followed by the Spacebar key, followed by the
Home key repeatedly until the cursor is at the start of the SQL
string. That's the string I use in the form code (with the possible
exception of replacing double quotes with single quotes). Maybe a key
got fatfingered during the process?

Here is the code I used behind a form's command button (all one line
for strSQL):

Dim strSQL As String
Dim MyDB As DAO.Database

strSQL = "INSERT INTO TEMPtblReport1 (GroupID, GroupName, CompanyID,
CompanyName, ProductID, ProductName, Quantity, ValueOfSale, Discount,[List Price], DateOfSale ) SELECT GroupID, GroupName, CompanyID,
CompanyName, ProductID, ProductName, Quantity, ValueOfSale, Discount,[List Price], DateOfSale FROM TempData;"
Set MyDB = CurrentDb()
MyDB.Execute strSQL, dbFailOnError
Do While MyDB.RecordsAffected = 0
DoEvents
Loop
Set MyDB = Nothing
MsgBox ("Done.")

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

We're gonna focus in on how the .NET Services and the SQL Data Service
use the Access Control Service.
....
Now there's one case, and there is a.. there's an unauthenticated
access capability of the Service Bus. But in the end, for most of the
features in the Service Bus, you gotta present a token. -- Justin
Smith, Sr. Program Manager, Microsoft, PDC08, BB55 :-)

On Tue, 7 Dec 2010 13:24:40 -0800 (PST), franc sutherland
<franc.sutherland (AT) googlemail (DOT) com> wrote:

Please post the SQL of the above query? Are there, for example, any
WHERE clauses referencing a form control?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

On Dec 8, 3:46*am, Tony Toews <tto... (AT) telusplanet (DOT) net> wrote:

Hi,

Here's the SQL behind the query

================================================

INSERT INTO TEMPtblReport1 ( GroupID, GroupName, CompanyID,
CompanyName, ProductID, ProductName, Quantity, ValueOfSale, Discount,[List Price], DateOfSale )
SELECT qryCompanyDiscountDateOfSale.GroupID,
qryCompanyDiscountDateOfSale.GroupName,
qrySalesCompaniesProducts.CompanyID,
qrySalesCompaniesProducts.CompanyName,
qrySalesCompaniesProducts.ProductID,
qrySalesCompaniesProducts.ProductName,
qrySalesCompaniesProducts.Quantity,
qrySalesCompaniesProducts.ValueOfSale,
CCur(([ValueOfSale]*[DiscountToApply])/100) AS Discount,
qryCompanyDiscountDateOfSale.[List Price],
qrySalesCompaniesProducts.DateOfSale
FROM qryCompanyDiscountDateOfSale
INNER JOIN qrySalesCompaniesProducts ON
(qryCompanyDiscountDateOfSale.GroupDiscountProduct ID =
qrySalesCompaniesProducts.ProductID)
AND (qryCompanyDiscountDateOfSale.GroupJoinCompanyID =
qrySalesCompaniesProducts.CompanyID)
AND (qryCompanyDiscountDateOfSale.DateOfSale =
qrySalesCompaniesProducts.DateOfSale)
ORDER BY qryCompanyDiscountDateOfSale.GroupID,
qrySalesCompaniesProducts.CompanyID,
qrySalesCompaniesProducts.ProductID;

================================================== =========================

qryCompanyDiscountDateOfSale has two references to text boxes on a
form, a 'from' and 'to' date

The SQL for qryCompanyDiscountDateOfSale is

================================================

SELECT qryAvgPrice.DateOfSale, qryCompanyDiscount.GroupID,
qryCompanyDiscount.GroupName, qryCompanyDiscount.GroupJoinCompanyID,
qryCompanyDiscount.GroupJoinStartDate,
qryCompanyDiscount.GroupJoinEndDate,
qryCompanyDiscount.GroupDiscountProductID,
qryCompanyDiscount.GroupDiscountStartDate,
qryCompanyDiscount.GroupDiscountEndDate,
IIf([GroupJoinStartDate]>[DateOfSale],
0,IIf([GroupJoinEndDate]<[DateOfSale],
0,IIf([GroupDiscountStartDate]>[DateOfSale],
0,IIf([GroupDiscountEndDate]<[DateOfSale],0,
[GroupDiscountPercentageDiscount])))) AS DiscountToApply,
qryAvgPrice.PricePrice AS[List Price]
FROM qryAvgPrice INNER JOIN qryCompanyDiscount ON
(qryAvgPrice.ProductID = qryCompanyDiscount.GroupDiscountProductID)
AND (qryAvgPrice.CompanyID = qryCompanyDiscount.GroupJoinCompanyID)
WHERE (((qryAvgPrice.DateOfSale) Between [Forms]![frmNewReports]!
[txtFrom] And [Forms]![frmNewReports]![txtTo]));

==================================================

When I change the form text box references to actual dates, the error
does not occur. What would be the best way to incorporate the user's
inputted dates into the query so that the append query will run?

Thanks,

Franc.

franc sutherland <franc.sutherland (AT) googlemail (DOT) com> wrote in
news:dd83adfc-fa0a-417a-aa11-b3e7306962e0 (AT) 29g2000prb (DOT) googlegroups.com
:

You should define the control references as parameters, for one.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Instead of using the execute method on the Append query try using code to open
the query

Docmd.OpenQuery "NameOfQuery"

Or if you want to you can build the query string and input the values of the
parameters. It is difficult to say what to do consider you are nesting
queries inside of nested queries.

qryAvgPrice, qryCompanyDiscount, qryCompanyDiscountDateOfSale,
qrySalesCompaniesProducts

The simple thing to do might be to have a saved query that you can modify by
changing its SQL.

Dim strSQL as String
strSQL = "SELECT qryAvgPrice.DateOfSale, qryCompanyDiscount.GroupID,
qryCompanyDiscount.GroupName, qryCompanyDiscount.GroupJoinCompanyID,
qryCompanyDiscount.GroupJoinStartDate,
qryCompanyDiscount.GroupJoinEndDate,
qryCompanyDiscount.GroupDiscountProductID,
qryCompanyDiscount.GroupDiscountStartDate,
qryCompanyDiscount.GroupDiscountEndDate,
IIf([GroupJoinStartDate]>[DateOfSale],
0,IIf([GroupJoinEndDate]<[DateOfSale],
0,IIf([GroupDiscountStartDate]>[DateOfSale],
0,IIf([GroupDiscountEndDate]<[DateOfSale],0,
[GroupDiscountPercentageDiscount])))) AS DiscountToApply,
qryAvgPrice.PricePrice AS[List Price]
FROM qryAvgPrice INNER JOIN qryCompanyDiscount ON
(qryAvgPrice.ProductID = qryCompanyDiscount.GroupDiscountProductID)
AND (qryAvgPrice.CompanyID = qryCompanyDiscount.GroupJoinCompanyID)"

Dim strWHere as String
strWHERE = "WHERE qryAvgPrice.DateOfSale Between " &
Format([Forms]![frmNewReports]![txtFrom],"\#yyyy-mm-dd\#") & " And " &
Format([Forms]![frmNewReports]![txtTo],"\#yyyy-mm-dd\#")

CurrentDb().QueryDefs("NameOfSavedquery").SQL = strSQL & strWhere

The use that saved query in place of qryCompanyDiscountDateOfSale or you could
chose to modify qryCompanyDiscountDateOfSale

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 12/8/2010 3:22 PM, David-W-Fenton wrote:

On Dec 8, 9:21*pm, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
Hi all,

Many thanks for your suggestions and comments. You were right about
referencing the form controls, and I changed that to variables (David
is that what you meant about defining the control references as
parameters?), building a string in VBA.
You were also right, that it was a bit of an endless well of nested
queries. Looking back over it, I realised there was a cleaner way of
doing it and re-jigged the initial query. This ran quicker and
allowed me to pass the date variables in a simpler manner.

build_sql is the string variable I am using to build the sql string
(from_date and to_date are variants converted to american date format
mm/dd/yyyy)

================================================== =======================================

build_sql = build_sql & "INSERT INTO TEMPtblReport1
( GroupID, GroupName, CompanyID, CompanyName, "
build_sql = build_sql & "ProductID, ProductName, Quantity,
ValueOfSale, Discount,[List Price], "
build_sql = build_sql & "DateOfSale ) "
build_sql = build_sql & "SELECT
qryCompanyDiscount.GroupID, qryCompanyDiscount.GroupName, "
build_sql = build_sql &
"qrySalesCompaniesProducts.CompanyID,
qrySalesCompaniesProducts.CompanyName, "
build_sql = build_sql &
"qrySalesCompaniesProducts.ProductID,
qrySalesCompaniesProducts.ProductName, "
build_sql = build_sql &
"qrySalesCompaniesProducts.Quantity,
qrySalesCompaniesProducts.ValueOfSale, "
build_sql = build_sql &
"[ValueOfSale]*(IIf([GroupJoinStartDate]>[qrySalesCompaniesProducts]."
build_sql = build_sql & "[DateOfSale],
0,IIf([GroupJoinEndDate]<[qrySalesCompaniesProducts]."
build_sql = build_sql & "[DateOfSale],
0,IIf([GroupDiscountStartDate]>[qrySalesCompaniesProducts]."
build_sql = build_sql & "[DateOfSale],
0,IIf([GroupDiscountEndDate]<[qrySalesCompaniesProducts]."
build_sql = build_sql & "[DateOfSale],0,
[GroupDiscountPercentageDiscount]))))/100) AS Discount, "
build_sql = build_sql & "qryCurrentPrice.PricePrice AS[List Price], "
build_sql = build_sql &
"qrySalesCompaniesProducts.DateOfSale FROM qryCurrentPrice INNER JOIN
"
build_sql = build_sql & "(qrySalesCompaniesProducts INNER
JOIN qryCompanyDiscount ON "
build_sql = build_sql &
"(qrySalesCompaniesProducts.ProductID = "
build_sql = build_sql &
"qryCompanyDiscount.GroupDiscountProductID) AND "
build_sql = build_sql &
"(qrySalesCompaniesProducts.CompanyID =
qryCompanyDiscount.GroupJoinCompanyID)) "
build_sql = build_sql & "ON qryCurrentPrice.PriceProductID
= qrySalesCompaniesProducts.ProductID "
build_sql = build_sql & "WHERE
(((qrySalesCompaniesProducts.DateOfSale) "
build_sql = build_sql & "Between " & date_from & " And " &
date_to & ")) "
build_sql = build_sql & "ORDER BY
qryCompanyDiscount.GroupID, qrySalesCompaniesProducts.CompanyID, "
build_sql = build_sql &
"qrySalesCompaniesProducts.ProductID;"

================================================== =============================================

John, thanks for the tip on how to concatenate more SQL onto the SQL
of an existing query. I hadn't seen that before. Very useful.

franc sutherland <franc.sutherland (AT) googlemail (DOT) com> wrote in
news:b392734f-4407-4b69-9bcc-788315253629 (AT) c17g2000prm (DOT) googlegroups.co
m:

No. This is what I mean by parameters:

PARAMETERS [Forms]![frmInventory]![InventoryID] Long;
SELECT tblInventory.*
FROM tblInventory
WHERE tblInventory.InventoryID=[Forms]![frmInventory]![InventoryID];

This makes the query optimizable, because the data type of the form
control is defined in the parameter. It also causes certain things
like Nulls in the referenced control to be handled in a more orderly
fashion than when the control is not defined as a parameter.

From A2002 on, it is essential that any control reference used in a
WHERE clause be defined as a parameter. Before that, it worked OK
without it, but something changed between A2000 and A2002 in the way
control references were handled that broke the old system and from
then on required the parameters in order to produce reliable
results.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On Dec 10, 3:39*am, "David-W-Fenton" <dfas... (AT) dfenton (DOT) com> wrote:
Hi David,

Thanks for that. That looks like a useful thing.
Can I ask what you meant by 'makes the query optimizable'?

Franc.

franc sutherland <franc.sutherland (AT) googlemail (DOT) com> wrote in
news:3a611a75-4370-4b42-87c6-2445ed9c577f (AT) k13g2000vbq (DOT) googlegroups.co
m:

For any saved QueryDef in Access, the database engine calculates a
query plan for executing the query in the most efficient manner
possible. When Jet/ACE doesn't know the data types of criteria, it
can't necessarily optimize the query in the most efficient manner
possible. With parameters, you remove that uncertainty, because the
parameter defines what data type the value in the WHERE clause will
be.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/