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=A0pm, franc sutherland <franc.sutherl...@googlemail.com>
wrote:
> Hi all,
>
> Using A2007. =A0I'm using an append query to load data into a temporary
> table.
> The query works fine when I run it from the query view. =A0But 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:
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> Dim db As Database
> =A0 =A0 =A0 =A0 =A0 =A0 Dim temp_table As TableDef
> =A0 =A0 =A0 =A0 =A0 =A0 Set db =3D DBEngine(0)(0)
> =A0 =A0 =A0 =A0 =A0 =A0 Set temp_table =3D db.CreateTableDef("TEMPtblRepo=
rt1")
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 With temp_table
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Grou=
pID", dbInteger)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Grou=
pName", dbText)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Comp=
anyID", dbDouble)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Comp=
anyName", dbText)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Prod=
uctID", dbText)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Prod=
uctName", dbText)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Valu=
eOfSale",
> dbCurrency)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Quan=
tity", dbInteger)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Disc=
ount",
> dbCurrency)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("List=
 Price",
> dbCurrency)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Fields.Append .CreateField("Date=
OfSale", dbDate)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 End With
>
> =A0 =A0 =A0 =A0 =A0 =A0 db.TableDefs.Append temp_table
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> then try to append data to it using
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> 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;
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> 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. =A0Can anyone help?
>
> Thanks,
>
> Franc.

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 =3D "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 =3D CurrentDb()
MyDB.Execute strSQL, dbFailOnError
Do While MyDB.RecordsAffected =3D 0
  DoEvents
Loop
Set MyDB =3D Nothing
MsgBox ("Done.")

James A. Fortune
CDMAPoster@FortuneJames.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@googlemail.com> wrote:
  
>FROM qryRetroGroupCompanyProductMonth;

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=A0am, Tony Toews <tto...@telusplanet.net> wrote:
> On Tue, 7 Dec 2010 13:24:40 -0800 (PST), franc sutherland
>
> <franc.sutherl...@googlemail.com> wrote:
> >FROM qryRetroGroupCompanyProductMonth;
>
> Please post the SQL of the above query? =A0Are 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
> =A0 updated seehttp://www.autofeupdater.com/


Hi,

Here's the SQL behind the query

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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.GroupDiscountProductID =3D
qrySalesCompaniesProducts.ProductID)
AND (qryCompanyDiscountDateOfSale.GroupJoinCompanyID =3D
qrySalesCompaniesProducts.CompanyID)
AND (qryCompanyDiscountDateOfSale.DateOfSale =3D
qrySalesCompaniesProducts.DateOfSale)
ORDER BY qryCompanyDiscountDateOfSale.GroupID,
qrySalesCompaniesProducts.CompanyID,
qrySalesCompaniesProducts.ProductID;

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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

The SQL for qryCompanyDiscountDateOfSale is

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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 =3D qryCompanyDiscount.GroupDiscountProductID)
AND (qryAvgPrice.CompanyID =3D qryCompanyDiscount.GroupJoinCompanyID)
WHERE (((qryAvgPrice.DateOfSale) Between [Forms]![frmNewReports]!
[txtFrom] And [Forms]![frmNewReports]![txtTo]));

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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@googlemail.com> wrote in
news:dd83adfc-fa0a-417a-aa11-b3e7306962e0@29g2000prb.googlegroups.com
: 

> 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? 

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:
> franc sutherland<franc.sutherland@googlemail.com>  wrote in
> news:dd83adfc-fa0a-417a-aa11-b3e7306962e0@29g2000prb.googlegroups.com
> :
>
>> 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?
>
> You should define the control references as parameters, for one.
>

On Dec 8, 9:21=A0pm, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> 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. =A0It 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 =3D "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 =3D qryCompanyDiscount.GroupDiscountProductID)
> AND (qryAvgPrice.CompanyID =3D qryCompanyDiscount.GroupJoinCompanyID)"
>
> Dim strWHere as String
> strWHERE =3D "WHERE qryAvgPrice.DateOfSale Between " &
> Format([Forms]![frmNewReports]![txtFrom],"\#yyyy-mm-dd\#") & " And " &
> Format([Forms]![frmNewReports]![txtTo],"\#yyyy-mm-dd\#")
>
> CurrentDb().QueryDefs("NameOfSavedquery").SQL =3D 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:
>
> > franc sutherland<franc.sutherl...@googlemail.com> =A0wrote in
> >news:dd83adfc-fa0a-417a-aa11-b3e7306962e0@29g2000prb.googlegroups.com
> > :
>
> >> When I change the form text box references to actual dates, the
> >> error does not occur. =A0What would be the best way to incorporate
> >> the user's inputted dates into the query so that the append query
> >> will run?
>
> > You should define the control references as parameters, for one.

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)

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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@googlemail.com> wrote in
news:b392734f-4407-4b69-9bcc-788315253629@c17g2000prm.googlegroups.co
m: 

> I changed that to variables (David
> is that what you meant about defining the control references as
> parameters?

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=A0am, "David-W-Fenton" <dfas...@dfenton.com> wrote:
> franc sutherland <franc.sutherl...@googlemail.com> wrote innews:b392734f-=
4407-4b69-9bcc-788315253629@c17g2000prm.googlegroups.co
> m:
>
> > I changed that to variables (David
> > is that what you meant about defining the control references as
> > parameters?
>
> No. This is what I mean by parameters:
>
> PARAMETERS [Forms]![frmInventory]![InventoryID] Long;
> SELECT tblInventory.*
> FROM tblInventory
> WHERE tblInventory.InventoryID=3D[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 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.com=
/
> contact via website only =A0 =A0http://www.dfenton.com/DFA/

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@googlemail.com> wrote in
news:3a611a75-4370-4b42-87c6-2445ed9c577f@k13g2000vbq.googlegroups.co
m: 

> On Dec 10, 3:39�am, "David-W-Fenton" <dfas...@dfenton.com> wrote:
>> franc sutherland <franc.sutherl...@googlemail.com> wrote
>> innews:b392734f- 
> 4407-4b69-9bcc-788315253629@c17g2000prm.googlegroups.co
>> m:
>>
>> > I changed that to variables (David
>> > is that what you meant about defining the control references as
>> > parameters?
>>
>> 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.
> 
> Thanks for that.  That looks like a useful thing.
> Can I ask what you meant by 'makes the query optimizable'?

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/

On Dec 11, 12:06=A0am, "David-W-Fenton" <dfas...@dfenton.com> wrote:
> franc sutherland <franc.sutherl...@googlemail.com> wrote innews:3a611a75-=
4370-4b42-87c6-2445ed9c577f@k13g2000vbq.googlegroups.co
> m:
>
>
>
> > On Dec 10, 3:39 am, "David-W-Fenton" <dfas...@dfenton.com> wrote:
> >> franc sutherland <franc.sutherl...@googlemail.com> wrote
> >> innews:b392734f-
> > 4407-4b69-9bcc-788315253...@c17g2000prm.googlegroups.co
> >> m:
>
> >> > I changed that to variables (David
> >> > is that what you meant about defining the control references as
> >> > parameters?
>
> >> No. This is what I mean by parameters:
>
> >> PARAMETERS [Forms]![frmInventory]![InventoryID] Long;
> >> SELECT tblInventory.*
> >> FROM tblInventory
> >> WHERE
> >> tblInventory.InventoryID=3D[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.
>
> > Thanks for that. =A0That looks like a useful thing.
> > Can I ask what you meant by 'makes the query optimizable'?
>
> 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 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.com=
/
> contact via website only =A0 =A0http://www.dfenton.com/DFA/

Thanks David.

On Thu, 9 Dec 2010 09:18:54 -0800 (PST), franc sutherland
<franc.sutherland@googlemail.com> wrote:
  
>            build_sql = build_sql & "INSERT INTO TEMPtblReport1
>( GroupID, GroupName, CompanyID, CompanyName, "
>            build_sql = build_sql & "ProductID, ProductName, Quantity,
>ValueOfSale, Discount, [List Price], "

Note that I do the following with such a construct:


            build_sql = build_sql & "INSERT INTO TEMPtblReport1
( GroupID, GroupName, CompanyID, CompanyName, " & _
           "ProductID, ProductName, Quantity, ValueOfSale, Discount,
[List Price], " & _
....

Makes it much more compact and easier to read.

Also note that as I never muck with the join lines I let those go
waaaaaaay over to the right hand side and just add the " & _
Although this might not apply to your query.

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/