Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    enter parameter value (access 2000)

    Eneter parameter value error

    I have the folloiwing valid sql

    StrBas = " SELECT DISTINCTROW Orders.OrderID, Sum([Order Details].[UnitPrice]*[Quantity]) AS Subtotal, Orders.customerid, Orders.orderdate, Sum([Order

    Details].cartons) AS SumOfcartons, Sum([Order Details].Quantity) AS SumOfQuantity, Sum([Order Details].liters) AS SumOfliters, customers.CompanyName,

    customers.afid " & _
    " FROM customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON customers.Customerid = Orders.customerid

    " & _
    "GROUP BY Orders.OrderID, Orders.customerid, Orders.orderdate, customers.CompanyName, customers.afid, Orders.paymentid " & _
    " HAVING (((Orders.customerid) " & strNotIn & " AND ((customers.afid)=1) AND ((Orders.paymentid)=False))"


    This sql works.However,i want to replace a part of with with another sql called SQLClass

    Dim SQLClass as string

    SQLClass = AND ((customers.afid)=1)

    And then

    StrBas = " SELECT DISTINCTROW Orders.OrderID, Sum([Order Details].[UnitPrice]*[Quantity]) AS Subtotal, Orders.customerid, Orders.orderdate, Sum([Order

    Details].cartons) AS SumOfcartons, Sum([Order Details].Quantity) AS SumOfQuantity, Sum([Order Details].liters) AS SumOfliters, customers.CompanyName,

    customers.afid " & _
    " FROM customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON customers.Customerid = Orders.customerid

    " & _
    "GROUP BY Orders.OrderID, Orders.customerid, Orders.orderdate, customers.CompanyName, customers.afid, Orders.paymentid " & _
    " HAVING (((Orders.customerid) " & strNotIn & " & SQLClass AND ((Orders.paymentid)=False))"


    But then i receive the message Enter parameter value SQLClass.Obvisoiuly my insertion is not accepted.If i take out the string SQLClass and
    replace the old AND ((customers.afid)=1) at its place,then it works again.
    May i have some help?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: enter parameter value (access 2000)

    Firstly the command
    SQLClass = AND ((customers.afid)=1)
    should probably be
    SQLClass = " AND ((customers.afid)=1)"
    Note the quotes and the space in front of the AND.

    The group by
    "GROUP BY Orders.OrderID, Orders.customerid, Orders.orderdate, customers.CompanyName, customers.afid, Orders.paymentid " & _
    " HAVING (((Orders.customerid) " & strNotIn & " & SQLClass AND ((Orders.paymentid)=False))"
    should probably read
    "GROUP BY Orders.OrderID, Orders.customerid, Orders.orderdate, customers.CompanyName, customers.afid, Orders.paymentid " & _
    " HAVING (((Orders.customerid) " & strNotIn & SQLClass & " AND ((Orders.paymentid)=False))"

    PAt <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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