Hi all, I have a query like this

[mmcg]

Hi all,
I have a query like this
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 give an error message saying
[Microsoft][ODBC Microsoft Access Driver]Too few parameters.
Expected 1

 

[CRoberts]

Are you in the right forum? Oracle syntax does not allow square brackets in queries, while Access does. You are using square brackets where a double quote is used by Oracle.

ACDB.ACCLT1> select * from "DUAL";

D
-
X

1 row selected.

ACDB.ACCLT1> select * from [dual];
select * from [dual]
*
ERROR at line 1:
ORA-00903: invalid table name

The next question I would ask myself, is the query itself at fault? Typically with a complex query like yours, I would first run and test it in SqlPlus. Once it is working properly there, then I would use that query in the application I was developing.

 

[revnice]

If this was Oracle the system would have a fit! The cost based optimizer would throw a tantrum. Even with bitmap indexes and clustered tables it would take forever to run. I think the problem is not the query but the structure of the db.

rev