Number of items in an IN clause?

[Jacob Havkrog]

Hi Group.

Is there a limit on how many items there can be in an IN clause, like

SELECT * FROM MyTable WHERE MyID IN (3, 6, 8, 12, 345, 444, 564, ..........)

I talk to MS Access through ADO from a Win32 application.

Thanks for your help.

Btw, is there a limit on how long, an SQL command can be?

Cheers
Jacob

 

[Arvin Meyer [MVP]]

I don't know if there's a limit for an In clause. I can tell you that I have
used more than 2 dozen of them)

In code there is a 2048 character limit in a SQL statement. I have run some
longer than that from the query window.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Jacob Havkrog]

Thanks - but to use hundreds, or thousands of items in the IN clause? Would
that be a problem?

Maybe I have to try it out....

Cheers
Jacob

 

[Douglas J. Steele]

I have to question how you would be generating an SQL statement with
hundreds or thousands of items in the IN clause. You'd be far better off
building a table of values to include, and join the two tables.

 

[PCD]

If your items have an autonumber primary key and your list of items are in
order that they were entered in the database, you could find the first and
last ID of the items in the list and then use Between FirstID And LastID as
criteria in your query.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
(e-mail address removed)

 

[david epsom dot com dot au]

There is certainly a character limit for the sql string. I don't
know what it is for ADO/Jet 4.0: for Jet 3.5 it was 1GB.

If you have a lot of values in an In list, you can rewrite
the list as a select clause:

where MyID IN (select n from tmptable)

(david)

 

[Jacob Havkrog]

Thanks for your replies. I will rewrite my code.

Rgds
Jacob Havkrog

 

[StopThisAdvertising]

"PCD" <[email protected]> schreef in bericht
--

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1150 users have come to me from the newsgroups requesting help '1150 users ????
(e-mail address removed)

--
To Steve:
Over 575!! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

 

[PCD]

All you are doing is INDEED showing every newsgroup reader that you are
nothing but an OBSESSED, egocentric A$$. You revel in tracking every post I
make in the newsgroups and do not make any contributions to our newsgroup.
Take the advise that was given to you by a poster not too long
ago and grow up and get a life. We don't need the likes of you in our
newsgroup! Just get lost and quit filling our newsgroup with your slanderous
garbage!!!!!!

INDEED!!!!!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
(e-mail address removed)


"PCD" <[email protected]> schreef in bericht

--

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
'Resource ????
Over 1150 users have come to me from the newsgroups requesting help
'1150 users ????
(e-mail address removed)

--
To Steve:
Over 575!! users from the newsgroups have visited the website to read what
kind of a 'resource' you are... (rapidly increasing..)

To the original poster:

Most people here have a common belief that the newsgroups are for *free
exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell
his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R