Problem with SQL (1 Viewer)

[muratas2]

Hi everybody,

I try to open a ADODB recordset writing below lines;

rs.Open "select * from tblItems where ItemId In (strList2)", _
cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

strList2 is a string consisting of concatanating selected Items' ItemData on
a previous form's list box. So it's like "'2','5','4'". The problem is,
ItemId field of tblItems is Autonumber. So ERROR OCCURED message appears.
When I change the SQL line to
....where ItemId In (2,6,5)
It works.
What should I do? I have a string and, naturally, I can't use it in SQL
to select a number field. Is there a way out for me?

Much thanks in advance

Murat

 

[Travis]

Try adding cLng (or convert long) to your query:
rs.Open "select * from tblItems where cLng(ItemId) In (strList2)", _
cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

 

[Pat Hartman]

You need to change your code so that it produces a string that looks like:
2,5,4
Then change the select statement so that Access will correctly parse the string list. SQL cannot do it.
rs.Open "select * from tblItems where ItemId In (" & strList2 & ")", _


If you add a variable and build the SQL string there, it will be easier for you to debug.
Dim strSQL as String
strSQL = "select * from tblItems where ItemId In (" & strList2 & ")"
rs.Open strSQL, _

That way you can look at the strSQL before you try to use it to make sure it is formated correctly.