Using the "In" criteria...

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have an account table with 500+ and growing account
numbers. Now we all now that attaching a local table to
the production tables REALLY slows things down. So what
I need to know is this. How many characters, I guess,
can you have in an "In" statement. In other words can I
have 500, 700, 1000 7 digit account numbers in one "In"
statement? If I can then I'll just write some vba to
create the query by placing all those accounts into the
statement but I cannot do that if I am limited by the
number of charaters you can have with an "In" statement.

Thanks.

Chris
 
I have an account table with 500+ and growing account
numbers. Now we all now that attaching a local table to
the production tables REALLY slows things down. So what
I need to know is this. How many characters, I guess,
can you have in an "In" statement. In other words can I
have 500, 700, 1000 7 digit account numbers in one "In"
statement? If I can then I'll just write some vba to
create the query by placing all those accounts into the
statement but I cannot do that if I am limited by the
number of charaters you can have with an "In" statement.
Click to expand...

The limit would be on the total size of the query - 64Kbytes will give
the dreaded "Query Too Complex" error.

I'd STRONGLY suggest not using this method! Performance will drag
compared with creating a table of account numbers, indexing the
account number in this "find it" table and in your main table, and
doing a Join. This has the same effect as the In() but should be
considerably more efficient.

If you need the IN, use the Subquery variant: rather than

WHERE AccountNo IN('112341A', '121301A', ...)

use

WHERE AccountNo IN (SELECT AccountNo FROM tblAccountsToSearch)
 
Back
Top