Help needed urgent

[mmcg]

Hi all,
I am using Access database and through VBA Coding I retrieve results of a SQL query into Excel.My problem is when I use a SQL Query with GROUP BY claus I get an error similar to
[Microsoft][ODBC Microsoft Access Driver]You tried to execute a query that does not include the specified expression 'Companyname' as part of an aggregate function.

But the same Query without GROUP BY clause works without any error.I dont know what the problem with GROUP BY clause is.Can someone help me?

 

[NatGreen]

Can you post your exact query?

It looks as if 'CompanyName' is not in the group by statement

Regards,
NatGreen

 

[mmcg]

sqlq = "SELECT Customers.CompanyName, Category.CategoryName, Customers.Area, Employees.FirstName, [Order Details].Quantity*[Order Details].UnitPrice, Orders.InvoiceNumber, [Payment Methods].PaymentMethod, Payments.PaymentAmount, Payments.PaymentDate, Products.ProductName, Orders.OrderDate,count(payments.paymentdate) " & _
"FROM Customers, Category, Employees, [Order Details], Orders, [Payment Methods], Payments, Products " & _
"WHERE Customers.CustomerID = Orders.CustomerID AND Employees.EmployeeID = Orders.EmployeeID AND [Order Details].OrderID = Orders.OrderID AND [Order Details].ProductID = Products.ProductID AND [Order Details].OrderID = Payments.OrderID AND " & _
"[Payment Methods].PaymentMethodID = Payments.PaymentMethodID AND Customers.CategoryID = Category.CategoryID GROUP BY Customers.CompanyName, Category.CategoryName, Customers.Area, Employees.FirstName, [Order Details].Quantity*[Order Details].UnitPrice, " & _
"Orders.InvoiceNumber, [Payment Methods].PaymentMethod, Payments.PaymentAmount, Payments.PaymentDate, Products.ProductName, Orders.OrderDate HAVING DATEPART(mm,payments.paymentdate)=" & Temp & ""
MsgBox ("SQL: " & sqlq)

It says
[Microsoft][ODBC Microsoft Access Driver]Too few parameters.
Expected 1

Can aanyone help me?

 

[Guest_imported]

When passing DatePart function in a SQL Statement using VBA or VB, use a single quote (or apostrophe) before and after the interval (eg: 'd'). This way the Access ODBC Driver will pass the SQL over to Access correctly.

I think this should fix the problem.