Make an Query in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The program that I am trying to modifiy is not my own and I can't redo
everything. with that said...

The problem:
I added a field "Service Charge" to and existing VBA code. This code will
create a Query. (I can't change the order of operations). IF you open the
query you will see that the "Service Charge" ->TOTAL box is set to
"Expression". This will give an error if you try to run the query.
However, If you change the TOTAL box to "Group by" then the query will work.
Question:
why would Access set the TOTAL field to "Expression" and/or how can I force
it to "Group by"?

This is day 3. Access help file is no use here.

Thank You
Scott Burke
(e-mail address removed)
 
If you are creating a query using VBA, you need to specify all the conditions
in the code. If you want Service Charge to Group By, then you need to
include that in GROUP BY clause in the SQL string used to create the query.
 
Hi Klatuu,
Thanks for your time. When I place "Service Charge" in "Group
by" part of the SQL statment.... It does not show up in the query.?! Maybe
there is something else in play that I did not notice. here is the acutual
SQL:
The "Service Charge" field shows up in the query but set to "Expression".
Oddly enuf... I took the orginal SQL and copied into the query wizard then
Then I switch to Design view. I added the "Service Charge" field. Then I
switch back to SQL View of the wazard. I found that "Service Charge" field
was in the same place it is in now.????


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


I must be missing a rule... I just don't know what rule.
any ideals?
Scott Burke
(e-mail address removed)
 
Never mind. I just reworte the entire section.
This was just one of those 5 min repairs. :)

Thanks for your time.
Scott
 
Back
Top