Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    extras in SQL clause (Access 2000)

    I have the following working sql clause:

    SELECT [order details].ProductID, Sum([order details].cartons) AS

    SumOfcartons, Sum([order details].Quantity) AS SumOfQuantity,

    customers.Customerid, customers.CompanyName, products.Productid,

    products.grade, orders.paymentid, orders.orderid, customers.afid,

    Sum(([Order Details].[UnitPrice]*[Quantity])) AS ExtendedPrice
    FROM products INNER JOIN ((customers INNER JOIN orders ON

    customers.Customerid = orders.customerid) INNER JOIN [order details] ON

    orders.orderid = [order details].OrderID) ON products.Productid =

    [order details].ProductID
    GROUP BY [order details].ProductID, customers.Customerid,

    customers.CompanyName, products.Productid, products.grade,

    orders.paymentid, orders.orderid, customers.afid
    HAVING (((customers.Customerid) Not In (118,120)) AND

    ((orders.paymentid)=0));


    I have made the following constant:
    Public Const strNotIn = " Not In (118;120))"


    Then i tried to replace it in the sql so:
    SELECT [order details].[ProductID], Sum([order details].[cartons]) AS

    SumOfcartons, Sum([order details].[Quantity]) AS SumOfQuantity,

    [customers].[Customerid], [customers].[CompanyName],

    [products].[Productid], [products].[grade], [orders].[paymentid],

    [orders].[orderid], [customers].[afid], Sum(([Order

    Details].[UnitPrice]*[Quantity])) AS ExtendedPrice
    FROM products INNER JOIN ((customers INNER JOIN orders ON

    [customers].[Customerid]=[orders].[customerid]) INNER JOIN [order

    details] ON [orders].[orderid]=[order details].[OrderID]) ON

    [products].[Productid]=[order details].[ProductID]
    GROUP BY [order details].[ProductID], [customers].[Customerid],

    [customers].[CompanyName], [products].[Productid], [products].[grade],

    [orders].[paymentid], [orders].[orderid], [customers].[afid]
    HAVING (((customers.Customerid)=strNotIN) AND ((orders.paymentid)=0));

    I received an error message Extra ) in query expression

    '(((Customers.Customerid)=strNotIn)) AND ((orders.paymentid) = 0));

    I cannot find the extra ) i have written in my expression.Also in the

    sql query i receive the message enter parameter value.
    Where are the extras in my code?I will be grateful for any help

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: extras in SQL clause (Access 2000)

    I don't understand why you want to replace the Not In phrase with a constant in the first place. Ordinarily, you would only replace something that was going to change and constants, by definition, don't change.

    Where is this SQL? If it's in a query, then you can't do what you're trying to do because you can't use variables (or constants) in queries. If it's in code, then the SQL needs to be in quotes with the constant concatenated to the SQL string.

    From the message you're getting, I assume that the SQL is in a query and the query engine is interpreting the strNotIn as a parameter. The way you typed it here, the string has a semicolon in it instead of a comma, and that may be what's causing your problem. The Access query engine will interpret the semicolon as the end of the SQL statement and everything beyond it as "extra".
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •