Max length af SQL

J

Jesper

I have some long SQLs in VBA using:
"SELECT from table WHERE id IN (1,2,3,4,5 etc)"
and they tends to get pretty long. What's the max length of an SQL
statement?
Thanks.

Jesper
 
D

Douglas J Steele

The help file for Access 97 lists "Number of characters in an SQL statement"
as "approximately 64,000". I can't see that having changed in newer
versions.
 
J

Jeff Boyce

Jesper

If you are using a SQL statement to generate the comma-separated list for
the IN() clause, have you considered adding an "embedded" SQL statement
("SELECT ... FROM ... WHERE ...") inside the IN() clause?

Regards

Jeff Boyce
<Access MVP>
 
J

Jesper

If you are using a SQL statement to generate the comma-separated list for
the IN() clause, have you considered adding an "embedded" SQL statement
("SELECT ... FROM ... WHERE ...") inside the IN() clause?
Click to expand...

Thanks Jeff and Doug. Yes I use an embedded "Select.." when I can, but these
numbers can't be fetched via SQL.
I'm wondering however if I could use EXISTS instead of IN and if they
perform differently?

Jesper Fjølner, Denmark
 
J

John Griffiths

"but these numbers can't be fetched via SQL"

Don't Believe you. :)

You have SELECT statement that matches a row/field to a value
but that value comes from code therefore there is a direct 1 to 1
relationship between the entity being expressed in code and
the entity (row) expressed in the database.

Please explain what you are doing so we can (give advice/become
enlightened) - John

This question has occurred before (google groups) and there is scope for
more
integer values in the IN() than the user can tick boxes on the UI.

You could produce a more elegant SQL statement to whatever you tastes are
by merging the ranges of integers like changing
IN( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
to BETWEEN 1 AND 10 and so on.
 
J

Jesper

Don't Believe you. :)
You have SELECT statement that matches a row/field to a value
but that value comes from code therefore there is a direct 1 to 1
relationship between the entity being expressed in code and
the entity (row) expressed in the database.

Please explain what you are doing so we can (give advice/become
enlightened) - John
Click to expand...

Thanks for the comments. I thought I had to use the IN solution in this
scenario, but I'd love to use a SELECT something instead.
I'd like to come back with a detailed description of the scenario, but
briefly - I'm using a modified version af Duane Hookom's At Your Survey
database, and it has a lot of data. About 3000 variables (questions).
Largest table about 400.000 rows.
From this I'm creating some forms for dynamic data extraction.
I'm trying to find a group of people who've answered A to one question, B to
that question etc. From this I generate a list of ID's for those people.
I'm unsure if something like this could be put into SQL, but it would be
great if it could.

Jesper F, Denmark.
 
J

John Griffiths

Hi Jesper

I don't know the details about the "I'm using a modified version af Duane
Hookom's At Your Survey database".

If we have a table of people we are interested in PERSON (ID, name, address
and so on).
A table of answers to questions (ANSWER) (question id , person id, answer
value)
A simple sub query will pick them out.

SELECT *
FROM Person p
WHERE p,ID IN(
SELECT a.PersonID FROM Answer a
WHERE a.Value = "YES"
)

OK? John
 
Top