I was afraid you'd say that.

I tried that on my first attempt, but my "IN string" ends up being too long. The IN field is 17 chars long and there can be any number of them. I came up with one "normal" instance where there are 65 items in the IN list.

My query crashed with an error that the query is too complicated(or something similar).

Any ideas for properly deleting the temp table?

I REALLY appreciate your suggestions and time.


>>Thank you for your response.
>>
>>The reason that I need a table is that I am using the data as a subquery.
>>
>>Can I use a recordset as a subquery? I couldn't seem to figure out how to do that.
>>
>>Okay, here's what I am trying to do. I have an some data that I need to be able to use as the IN clause of a different SELECT. The data gathered for the IN clause has to be generated procedurally, and cannot be done in a single select. I can put that data in a recordset, an array or a temp table. If I put it in a temp table, I can't delete the temp table. If I put it in an array or a recordset, I don't know how to use that as a subquery for the IN clause.
>>
>>The recordset method would be a wonderful solution, but I can't figure out how to use it in a subquery.
>>
>>Any ideas?
>>
>>Thank you!
>
>You can't use the ADO recordset as a subquery, but you can loop through it and concatenate a string that will contain your list for the IN clause. Some pseudo-code:
>
>strIN=""
>Do While Not oRS.EOF
>strIN = strIN & "'" & oRS("myField") & "',"
>oRS.MoveNext
>Loop
>
>And then remove the last comma from strIN and concatenate with the rest of the select clause as in "select * from myTable Where myField In (" & strIN & ")".
>
>If you put it in an array it may be easier to use the Split or Join function to create your list.