SQL sum method error

[zann]

Hi all

I am trying to use a query in a vb app, that uses ADO. I have successfully created and executed the query in ms access. But when i try run the query from my VB code I get an ODBC error. The problem is (at least I think) the sum method. I am not sure if it is supported by ODBC and ADO.

Here is the SQL statement:
SELECT Seller, Sum(quantity*price) FROM transaction
WHERE AuctionName ='ttt' AND AuctionDate = '11/18/2002'
GROUP BY Seller
ORDER BY Sum(quantity*price) DESC;

If i take the Sum part out the query works but I obviously need it so is there any way of using the Sum method or another alternative.

thanx in advance.

 

[mp9]

I haven't tested it but how about:

SELECT Seller, Sum(quantity*price) AS Cost FROM transaction
WHERE AuctionName ='ttt' AND AuctionDate = '11/18/2002'
GROUP BY Seller
ORDER BY Cost DESC;

http://www22.brinkster.com/accessory/

 

[JeremyNYC]

Dates have to be offset with #, not '.

Try that and see what happens.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cmmrfrds]

ADO does not have a problem with this syntax, but as pointed out by Jeremy Access needs # around the date fields.

SELECT Seller, Sum(quantity*price) AS Cost FROM transaction
WHERE AuctionName ='ttt' AND AuctionDate = '11/18/2002'
GROUP BY Seller
ORDER BY Sum(quantity*price) DESC;

I don't believe Access will accept "Cost" in the Order by clause.