SQL problems (Access 2000)

Topic

New Reply

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,698,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

Reply | Quote

Replies

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[/i]…WHERE (((orders.customerid) ” & strBuffer & ” AND ((orders.paymentid)=0) AND…filler[/i]…”

Reply | Quote