VBA to Query.

G

Guest

I am updating a program. The program uses VBA to create a query. The
problem is that the orders.[service charge] is being generated with the TOTAL
field = "Expression". If I go into the query design view and change the
TOTAL field to "Group by" the query works perfectly.

Question. Why wont the "Service Charge" be created with TOTAL = "Group by"?
Here is the VBA that creates the query.


Set qd = db.CreateQueryDef("qryCommissionReport")
strSQL = "SELECT IIf(IsNull([SHIPNAME]),[COMPANYNAME],[SHIPNAME]) AS
NAME, "
strSQL = strSQL & "Employees.EmplCode, Orders.PCCOrderNumber,
Orders.[Service Charge], "
strSQL = strSQL & " [FIRSTNAME] & ' ' & [LASTNAME] AS SALESPRSN, "
strSQL = strSQL & "Orders.NewCust, [Order Details].ClearDate2, "
strSQL = strSQL & "Products.ProductName, Customers.ContractNumber, "
strSQL = strSQL & "Sum([Order Details].Quantity) AS SumOfQuantity,
sum(orders.[service charge]) as TOTALSC, "
strSQL = strSQL & "[Order Details].UnitPrice, [Order Details].RAmount, "
strSQL = strSQL & "Employees.Ren, Employees.New, "
strSQL = strSQL & "IIf([REBATE] Is Null,([ORDER
DETAILS].UNITPRICE-[RAMOUNT])*"
strSQL = strSQL & "[QUANTITY],((([ORDER
DETAILS].UNITPRICE-[RAMOUNT])*[QUANTITY])"
strSQL = strSQL & "-[REBATE])) AS PROFIT, "
strSQL = strSQL & "[Order Details].ClearDate, Employees.Location, "
strSQL = strSQL & "Sum(Orders.PkgAmt) AS SumOfPkgAmt, Employees.Cap, "
strSQL = strSQL & "IIf(Not
IsNull([SHIPSTATEORPROVINCE]),[SHIPSTATEORPROVINCE],"
strSQL = strSQL & "[STATEORPROVINCE]) AS STATE "
strSQL = strSQL & "FROM Products INNER JOIN ((Customers INNER JOIN
(Employees "
strSQL = strSQL & "RIGHT JOIN Orders ON Employees.EmployeeID =
Orders.EmployeeID) "
strSQL = strSQL & "ON Customers.CustomerID = Orders.CustomerID) INNER
JOIN "
strSQL = strSQL & "[Order Details] ON Orders.OrderID = [Order
Details].OrderID) ON "
strSQL = strSQL & "Products.ProductID = [Order Details].ProductID "
strSQL = strSQL & "GROUP BY
IIf(IsNull([SHIPNAME]),[COMPANYNAME],[SHIPNAME]), "
strSQL = strSQL & "Employees.EmplCode, Orders.PCCOrderNumber,
Orders.[Service Charge], "
strSQL = strSQL & "[FIRSTNAME] & ' ' & [LASTNAME], "
strSQL = strSQL & "Orders.NewCust, [Order Details].ClearDate2, "
strSQL = strSQL & "Products.ProductName, Customers.ContractNumber, "
strSQL = strSQL & "[Order Details].UnitPrice, [Order Details].RAmount, "
strSQL = strSQL & "Employees.Ren, Employees.New, "
strSQL = strSQL & "IIf([REBATE] Is Null,([ORDER
DETAILS].UNITPRICE-[RAMOUNT])*"
strSQL = strSQL & "[QUANTITY],((([ORDER
DETAILS].UNITPRICE-[RAMOUNT])*[QUANTITY])"
strSQL = strSQL & "-[REBATE])), "
strSQL = strSQL & "[Order Details].ClearDate, Employees.Location,
Employees.Cap, "
strSQL = strSQL & "IIf(Not
IsNull([SHIPSTATEORPROVINCE]),[SHIPSTATEORPROVINCE],"
strSQL = strSQL & "[STATEORPROVINCE]) "
strSQL = strSQL & "HAVING Employees.EmplCode = 'JB' "
strSQL = strSQL & "AND Orders.NewCust = True "
strSQL = strSQL & "AND [Order Details].ClearDate2 Between #2/1/2005# And
#"
strSQL = strSQL & Me.txtEClearDate & "# "
strSQL = strSQL & "AND Customers.ContractNumber <> 'HON-KIP' "
strSQL = strSQL & "AND Employees.Location = 'IN' "
strSQL = strSQL & "ORDER BY Orders.PCCOrderNumber;"
qd.SQL = strSQL
 
D

Douglas J. Steele

You got Orders.[Service Charge] as a field in your query, plus you've got
sum(orders.[service charge]) as TOTALSC. Realistically, that's pointless. I
think Access is simply getting confused by the two different references to
orders.[service charge].

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Scott Burke said:
I am updating a program. The program uses VBA to create a query. The
problem is that the orders.[service charge] is being generated with the
TOTAL
field = "Expression". If I go into the query design view and change the
TOTAL field to "Group by" the query works perfectly.

Question. Why wont the "Service Charge" be created with TOTAL = "Group
by"?
Here is the VBA that creates the query.


Set qd = db.CreateQueryDef("qryCommissionReport")
strSQL = "SELECT IIf(IsNull([SHIPNAME]),[COMPANYNAME],[SHIPNAME]) AS
NAME, "
strSQL = strSQL & "Employees.EmplCode, Orders.PCCOrderNumber,
Orders.[Service Charge], "
strSQL = strSQL & " [FIRSTNAME] & ' ' & [LASTNAME] AS SALESPRSN, "
strSQL = strSQL & "Orders.NewCust, [Order Details].ClearDate2, "
strSQL = strSQL & "Products.ProductName, Customers.ContractNumber, "
strSQL = strSQL & "Sum([Order Details].Quantity) AS SumOfQuantity,
sum(orders.[service charge]) as TOTALSC, "
strSQL = strSQL & "[Order Details].UnitPrice, [Order Details].RAmount,
"
strSQL = strSQL & "Employees.Ren, Employees.New, "
strSQL = strSQL & "IIf([REBATE] Is Null,([ORDER
DETAILS].UNITPRICE-[RAMOUNT])*"
strSQL = strSQL & "[QUANTITY],((([ORDER
DETAILS].UNITPRICE-[RAMOUNT])*[QUANTITY])"
strSQL = strSQL & "-[REBATE])) AS PROFIT, "
strSQL = strSQL & "[Order Details].ClearDate, Employees.Location, "
strSQL = strSQL & "Sum(Orders.PkgAmt) AS SumOfPkgAmt, Employees.Cap, "
strSQL = strSQL & "IIf(Not
IsNull([SHIPSTATEORPROVINCE]),[SHIPSTATEORPROVINCE],"
strSQL = strSQL & "[STATEORPROVINCE]) AS STATE "
strSQL = strSQL & "FROM Products INNER JOIN ((Customers INNER JOIN
(Employees "
strSQL = strSQL & "RIGHT JOIN Orders ON Employees.EmployeeID =
Orders.EmployeeID) "
strSQL = strSQL & "ON Customers.CustomerID = Orders.CustomerID) INNER
JOIN "
strSQL = strSQL & "[Order Details] ON Orders.OrderID = [Order
Details].OrderID) ON "
strSQL = strSQL & "Products.ProductID = [Order Details].ProductID "
strSQL = strSQL & "GROUP BY
IIf(IsNull([SHIPNAME]),[COMPANYNAME],[SHIPNAME]), "
strSQL = strSQL & "Employees.EmplCode, Orders.PCCOrderNumber,
Orders.[Service Charge], "
strSQL = strSQL & "[FIRSTNAME] & ' ' & [LASTNAME], "
strSQL = strSQL & "Orders.NewCust, [Order Details].ClearDate2, "
strSQL = strSQL & "Products.ProductName, Customers.ContractNumber, "
strSQL = strSQL & "[Order Details].UnitPrice, [Order Details].RAmount,
"
strSQL = strSQL & "Employees.Ren, Employees.New, "
strSQL = strSQL & "IIf([REBATE] Is Null,([ORDER
DETAILS].UNITPRICE-[RAMOUNT])*"
strSQL = strSQL & "[QUANTITY],((([ORDER
DETAILS].UNITPRICE-[RAMOUNT])*[QUANTITY])"
strSQL = strSQL & "-[REBATE])), "
strSQL = strSQL & "[Order Details].ClearDate, Employees.Location,
Employees.Cap, "
strSQL = strSQL & "IIf(Not
IsNull([SHIPSTATEORPROVINCE]),[SHIPSTATEORPROVINCE],"
strSQL = strSQL & "[STATEORPROVINCE]) "
strSQL = strSQL & "HAVING Employees.EmplCode = 'JB' "
strSQL = strSQL & "AND Orders.NewCust = True "
strSQL = strSQL & "AND [Order Details].ClearDate2 Between #2/1/2005#
And
#"
strSQL = strSQL & Me.txtEClearDate & "# "
strSQL = strSQL & "AND Customers.ContractNumber <> 'HON-KIP' "
strSQL = strSQL & "AND Employees.Location = 'IN' "
strSQL = strSQL & "ORDER BY Orders.PCCOrderNumber;"
qd.SQL = strSQL
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code works in query but not in vba - help 4
Crosstab subcolumn query/report heading pblm 3
Report totals off by a couple cents. 4
Line-Break VBA to pass to a Query 6
percentage calculation...trying again 26
percentage calculation 2
NOT criteria in query not working 5
2 crosstabs 1

Top