Limit of query length

[Guest]

Hi,
does anyone know if there is a limit to the length of a query? For instance,
suppose a query like:

Select * From Clients where ClientID IN (strOverseasClients)

where strOverseasClients is a string containing thousands of comma delimited
client IDs. Is there a point where Access will say that the query contains
too many characters to process?

thnks

 

[Allen Browne]

If you search the Access help for "Specifications", you see these limits
(amongst others) for queries:
SQL statement: around 64K characters.
ANDs in WHERE/HAVING clause: 99

No limit is listed for the number of items in an IN phrase. If I understand
correctly, these are treated as array elements, so there should not be a
problem with any index size that is practical within the overall limit of
64k characters in the entire SQL statement.

 

[Guest]

Thanks Allen,

one more thing, does this limitation also applies SQL string when you use
docmd.runsql? or only when you create query through database window?

 

[Allen Browne]

If you use the RunSQL action, I think you are limited to 255 characters.