Array to "IN" operator

[Kevin Chambers]

Hi all--

Quick question: has anyone come up with an easy way to take an array
and use its elements as part of a WHERE clause? For example:
<This obviously doesn't work>
SELECT *
FROM Table1
WHERE Field1 IN Array(1,2,3)

The only solution I can think of is putting the values in a temp table
and then doing this:
SELECT *
FROM Table1
WHERE Field1 IN (SELECT Val FROM TTable)

But it would be nice to not have to go through all that.

Thoughts?

Thanks in advance,
KC

[RoyVidar]

"Kevin Chambers" <KB********@gmail.comwrote in message
<11**********************@i15g2000cwa.googlegroups .com>:

Hi all--

Quick question: has anyone come up with an easy way to take an array
and use its elements as part of a WHERE clause? For example:
<This obviously doesn't work>
SELECT *
FROM Table1
WHERE Field1 IN Array(1,2,3)

The only solution I can think of is putting the values in a temp
table and then doing this:
SELECT *
FROM Table1
WHERE Field1 IN (SELECT Val FROM TTable)

But it would be nice to not have to go through all that.

Thoughts?

Thanks in advance,
KC

Doesn't this work for numbers?

SELECT *
FROM Table1
WHERE Field1 IN (1,2,3)

and this for text?

WHERE Field1 IN ('1','2','3')

If you mean stuff it from a (string) array in VB

....WHERE Field1 IN (" & join(myarray, ",") & ")"

--
Roy-Vidar

[Kevin Chambers]

Yes, that would certainly work to concatenate a SQL string like that,
and use that as the basis for creating a recordset. I was more
wondering about something I could pass in to a saved Access query
object via parameters or a function that returns an array. If I used
join(array()) as a parameter, the query would take the entire string
and evaluate as one value, ie, Field1 = '''1'',''2'',''3'''.

It seems like the only solution is to do it the way you describe or go
with a temp table.

Unless there are any other bright ideas?

RoyVidar wrote:

"Kevin Chambers" <KB********@gmail.comwrote in message
<11**********************@i15g2000cwa.googlegroups .com>:

Hi all--

Quick question: has anyone come up with an easy way to take an array
and use its elements as part of a WHERE clause? For example:
<This obviously doesn't work>
SELECT *
FROM Table1
WHERE Field1 IN Array(1,2,3)

The only solution I can think of is putting the values in a temp
table and then doing this:
SELECT *
FROM Table1
WHERE Field1 IN (SELECT Val FROM TTable)

But it would be nice to not have to go through all that.

Thoughts?

Thanks in advance,
KC

Doesn't this work for numbers?

SELECT *
FROM Table1
WHERE Field1 IN (1,2,3)

and this for text?

WHERE Field1 IN ('1','2','3')

If you mean stuff it from a (string) array in VB

...WHERE Field1 IN (" & join(myarray, ",") & ")"

--
Roy-Vidar

[Marshall Barton]

It won't be any too quick, but for simple list of one digit
items in the list you could use a calculated field in the
query (with its Show box unchecked):

InStr(Forms!aform.txtstring, Field1) 0

Or if any value in the field might match just a part of any
item in the list:

InStr("," & Forms!aform.txtstring & ",", "," & Field1 & ",")

0

If the field can not contain any wildcard characters, you
could also use Like instead of Instr:

"," & Forms!aform.txtstring & "," Like "*," & Field1 & ",*"

aform is an open form and txtstring is a text box on the
form where you have parked the results of the Join function.
--
Marsh
Kevin Chambers wrote:

>Yes, that would certainly work to concatenate a SQL string like that,
and use that as the basis for creating a recordset. I was more
wondering about something I could pass in to a saved Access query
object via parameters or a function that returns an array. If I used
join(array()) as a parameter, the query would take the entire string
and evaluate as one value, ie, Field1 = '''1'',''2'',''3'''.

It seems like the only solution is to do it the way you describe or go
with a temp table.

Unless there are any other bright ideas?

RoyVidar wrote:

>"Kevin Chambers" <KB********@gmail.comwrote in message
<11**********************@i15g2000cwa.googlegroup s.com>:

Hi all--

Quick question: has anyone come up with an easy way to take an array
and use its elements as part of a WHERE clause? For example:
<This obviously doesn't work>
SELECT *
FROM Table1
WHERE Field1 IN Array(1,2,3)

The only solution I can think of is putting the values in a temp
table and then doing this:
SELECT *
FROM Table1
WHERE Field1 IN (SELECT Val FROM TTable)

But it would be nice to not have to go through all that.

Thoughts?

Thanks in advance,
KC

Doesn't this work for numbers?

SELECT *
FROM Table1
WHERE Field1 IN (1,2,3)

and this for text?

WHERE Field1 IN ('1','2','3')

If you mean stuff it from a (string) array in VB

...WHERE Field1 IN (" & join(myarray, ",") & ")"

--
Roy-Vidar