Opening a recordset based on an SQL statement

[Everton]

Hi
I am trying to open a recordset based on a query, but using an SQL statement to only retrieve certain records. I am using VBA to create a string with the criteria from a list box. When the code runs, the recordset contains all the records from the "parts" query instead of just the selected records. Here is the part of code with the problem:

strselcabs = ""
For Each selcab In Me!cablst.ItemsSelected 'creates a string criteria
cab = Me!cablst.ItemData(selcab)
strselcabs = strselcabs & Chr(34) & cab & Chr(34) & " OR "
Next
strselcabs = Left(strselcabs, (Len(strselcabs) - 4)) 'strips the last " OR " from the string criteria

Set rstcabpts = dbs.OpenRecordset("SELECT * FROM parts WHERE [CABLE ID]= " & strselcabs, dbOpenDynaset)

I am not too familiar with SQL, so imagine the problem lies there. Any help appreciated.

Cheers,
Paul.

 

[Everton]

OK, Ive solved it. I have made it so the SQL reads like

SELECT * FROM parts WHERE [CABLE ID]= A OR [CABLE ID]= B OR [CABLE ID]= C OR etc..

Rather than:
SELECT * FROM parts WHERE [CABLE ID]= A OR B OR C OR etc..

If there is a better solution let me know.
Paul.

 

[Everton]

Thanks. Would this make the SQL run faster? I imagine so, but the difference for my code will probably be negligable as it will only be used for a handful of criteria at a time.

Cheers,
Paul.

 

[Jon Holmes]

The other advantage of an IN statement is that SQL statements are limited to around 2000 characters. This allows more criteria to be included - especially if the field name is longish.
Jon