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

    SQL problems (Access 2000)

    SQL problems

    I have the following valid SQL Clause :
    ListLondon = "SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid, orders.paymentid, affiliates.afid FROM affiliates INNER JOIN (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) ON affiliates.afid = customers.afid WHERE (((orders.customerid) Not In (118,119,120,121,221,36,6,48,69,123,699,700,701,69 8,40,122,124)) AND ((orders.paymentid)=0) AND ((affiliates.afid)=2)) ORDER BY orders.orderdate"

    I want to replace the NotIn part like that:

    Dim strBuffer
    strBuffer = " Not In 118,119,120,121,221,36,6,48,69,123,699,700,701,698 ,40,122,124))"

    And then I wanted to replace it in the clause like that:
    ListBulems = "SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid, orders.paymentid, affiliates.afid FROM affiliates INNER JOIN (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) ON affiliates.afid = customers.afid WHERE (((orders.customerid) & strBuffer AND ((orders.paymentid)=0) AND ((affiliates.afid)=1)) ORDER BY orders.orderdate"

    However it doesn

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL problems (Access 2000)

    Little tough without knowing the exact error message you're getting, but I did notice a couple of things:

    >>>strBuffer = " Not In 118,119,120,121,221,36,6,48,69,123,699,700,701,698 ,40,122,124))"

    There doesn't seem to be any opening parentheses here.

    >>>"...WHERE (((orders.customerid) & strBuffer AND ((orders.paymentid)=0) AND..."

    Because you're now concatenating a variable to a string that is a SQL statement, you need to go about it a bit differently.

    ListBulems = "SELECT...filler...WHERE (((orders.customerid)<font color=448800> " & strBuffer & " </font color=448800>AND ((orders.paymentid)=0) AND...filler..."
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

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