build sql where clause from pasted text box entries..

[Guest]

Hi,
I'm trying to build a parameterized sql from data pasted into a text box.
The data can be from 50 to over 300 record keys, which they will want to
export to excel.
what would be best route, or another if not possible..
thansks!

 

[Marshall Barton]

I'm trying to build a parameterized sql from data pasted into a text box.
The data can be from 50 to over 300 record keys, which they will want to
export to excel.
what would be best route, or another if not possible..

The list must have a specific delimiter between the keys.
Comma is most convenient so I will assume that's required.

If you are using VBA to construct the query's SQL statement,
you can just concatentate it in to the IN operator.
Although I have no idea if this can deal with 300 values:

strSQL="SELECT . . . WHERE key IN(" & thetextbox & ")"

What you are going to do with the resulting SQL string
depends on the details of what else you have going on in
your code.

A different way that can be used as a parameter directly in
the query is to use a calculated field:
InStr("," & Forms!theformtextbox & ",", "," & key & ",")
with a criteria of: