Maximum "In" values allowed

[eit...@gmail.com]

Hi

I am using the In or Not in statement in Microsoft Access SQL and I am
curious to know how many arguments I can have? (Currently up to around
3500 and growing quickly)

I would like to find out before the statement overflows or if it will
even overflow.

Eitan

[david epsom dot com dot au]

To get FAST answers to questions like this, go to

http://groups.google.com.au/

and paste in

"how many" "in clause" group:*access*

Which will tell you that you are only limited by the number of arguments you
can type in,
and that you would be better off using a database, rather than typing all
the data into a string.

:~)

(david)

<eit...@gmail.com> wrote in message
news:1144738585.4...@z34g2000cwc.googlegroups.com...

[TC]

Holy cripes, why would you want anything remotely near to that number?

Sounds to me like you need a subquery. For example, this returns all
records from table A where there is a record in table B with field
B.zzz equal to field xxx of that record in A:

SELECT ..
FROM A
WHERE EXISTS
( SELECT NULL
FROM B
WHERE B.zzz = A.xxx )

The "nested" SELECT is called a subquery.

HTH,
TC (MVP Access)
http://tc2.atspace.com

[eit...@gmail.com]

Yes granted, and point taken except i was running my query like that..
I was running a sub query in a NOT IN statement... Unforunately it was
much to slow, it was taking a minute and a half to return a result...
which through code i could fashion in 2-3 seconds (using various
queries and arrays put together)

As far as i understood, the statement ran the subquery for every result
of the ID i was querying. Therefore creating the long waiting time for
the result...

Once again though, if im using a subquery or not in an IN (Not In)
statement im still left with the question, how many arguments can it
recieve.. I have read that it is not dependent on the IN (NOT IN)
statement but rather the size of the entire query that would create an
overflow.

[TC]

[eit...@gmail.com]

David, your point is seen and essentially answers my question.

Thanks mate :)

[TC]

Slow queries are usually due to incorrect primary keys and/or wrongly
written SQL. Believe me - MS Jet can execute enormously complex
queries, almost instantly, as long as the tables are primary-keyed
correctly & the SQL is written correcty.

I've never seen a case where it was necessary to use an IN clause with
3500 elements, to make a query faster. It's a tribute to MS Jet, that
it will even *execute* an IN clause with that many elements!

PS. Sorry for all the duplicate psts - google groups was playing up.

[Dirk Goldgar]

"TC" <gg.20.k...@spamgourmet.com> wrote in message
news:1144749219.2...@e56g2000cwe.googlegroups.com

> Slow queries are usually due to incorrect primary keys and/or wrongly
> written SQL. Believe me - MS Jet can execute enormously complex
> queries, almost instantly, as long as the tables are primary-keyed
> correctly & the SQL is written correcty.

True, but Jet does have trouble optimizing queries with NOT IN
subqueries. Usually it's better to use what someone has called the
"frustrated outer join" approach; e.g.,

SELECT ..
FROM A
LEFT JOIN B
ON A.zzz = B.xxx
WHERE B.zzz IS NULL

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

[TC]

Sure, but I was recommending [NOT] EXISTS - not [NOT] IN :-)

Cheers,

[Dirk Goldgar]

"TC" <aatcbb...@yahoo.com> wrote in message
news:1144763905.7...@i40g2000cwc.googlegroups.com

> Sure, but I was recommending [NOT] EXISTS - not [NOT] IN :-)

I'm NOT sure if that makes a difference or NOT. <g> It'd be worth
checking out, though, just to know.