Duhhhhh (whack!, whack!, whack!) There, now that I gave myself the 3 cuffs I needed.

I've obviously been looking at this to long. I just clued back in that this is a concatenated string and not evaluating (" + mcproj + ") in an actual SQL-SELECT.

I think I'll go to the pub for lunch for a few pints.

Thanks again to you and Sylvain.

>For it to work, the string has to ultimately not have the extra set of double-quotes surrounding the individual values which are surrounded by the single-quotes. If you do something like:
>
>lcSQL = "SELECT * FROM ZAKI.PROJ Proj WHERE {fn LEFT(Proj.PROJ_ID,6)} IN (" + mcproj + ") AND {fn LENGTH(Proj.PROJ_ID)}=10 ORDER BY Proj.PROJ_ID"
>
>? lcSQL
>
>...you will be able to see the actual string sent to the back-end. The killer is when the variable sent to the backend has the single-quotes inside of double-quotes.
>
>
>>Thanks Sylvain. I tried this and it worked. Can you explain to me how the IN (" + mcproj + ") is being evaluated? I never would have thought of this and don't really understand why it works.
>>
>>>Since you send a string to SQLServer, I don't see why you could not do this:
>>>
>>>

>>>mcproj="'000609','000172','001113'"
>>>
>>>? mcproj
>>>? SQLEXEC(1,"SELECT *;
>>> FROM ZAKI.PROJ Proj;
>>> WHERE {fn LEFT(Proj.PROJ_ID,6)} IN (" + mcproj + ") AND {fn LENGTH(Proj.PROJ_ID)}=10;
>>> ORDER BY Proj.PROJ_ID")
>>>

>>>
>>>
>>>>Can anyone tell me why the first SQL statement returns 17 records and the second returns none? Both SQLEXEC()'s return 1 so there are no errors in either SELECT.

Colin Magee
Team Leader, Systems Development
Metroland Media Group Ltd.
Mississauga, Ontario, Canada

cmagee@metroland.com

Never mistake having a career with having a life.