Extra Select Query Criteria Parameters

[Charle...@gmail.com]

Hi,

Access provdies 9 criteria paramaters counting down towards the bottom.
I need more criteria fields. Is it possible to add more critera
fields so I can make a larger query? Thanks

Charles

[G. Vaught]

Don't forget you can go horizontal also, such as >=1 and <=25 OR >=100 AND
<=200

However, if you need more than Access provides, then something may be wrong
with your table designs.

<Charle...@gmail.com> wrote in message
news:1153516383.5...@i3g2000cwc.googlegroups.com...

[John Vinson]

On Fri, 21 Jul 2006 19:52:21 -0600, "G. Vaught" <glva...@hotmail.com>
wrote:

>> Access provdies 9 criteria paramaters counting down towards the bottom.
>> I need more criteria fields. Is it possible to add more critera
>> fields so I can make a larger query? Thanks

Select all nine rows; select Insert... Rows from the menu. Hey presto,
nine more rows.

Or, you can go into SQL view and insert more OR clauses; or, you can
use a criterion such as

IN (1,3, 6, 8, 12, 41)

I do agree that your table structure might be at the root of the
problem though! Care to describe it, and indicate what you need more
than nine OR clauses for?

John W. Vinson[MVP]

[Jamie Collins]

John Vinson wrote:
> >> Access provdies 9 criteria paramaters counting down towards the bottom.
> >> I need more criteria fields. Is it possible to add more critera
> >> fields so I can make a larger query? Thanks
>
> Select all nine rows; select Insert... Rows from the menu. Hey presto,
> nine more rows.
>
> Or, you can go into SQL view and insert more OR clauses; or, you can
> use a criterion such as
>
> IN (1,3, 6, 8, 12, 41)

How far does this 'nine more rows' go?

According to the Jet 4.0 specification the maximum number of parameters
for a PROCEDURE ('parameter query' in Access-speak) is ...

Only joking of course. MSFT never provided a specification therefore we
have to work out for ourselves what the outer limits of the product
are. They provided help but I've seen so many errors now I merely see
it as a starting point:

CREATE PROCEDURE Statement
http://office.microsoft.com/en-us/assistance/HP010322191033.aspx

"From one to 255 field names or parameters"

I suppose we should assume 255 to be the supported limit (remembering
that Jet is a deprecated component and therefore you shouldn't count on
too much actual 'support'). Yet a simple test shows that 256 parameters
are legal.

Knowing that Jet 4.0 was developed (and is still owned) by the SQL
Server team, the SQL Server limit of 2100 parameters is the next step.
Again, no real problems.

After a little more testing I conclude that the number of parameters
for a procedure is effectively limitless. With the help of Excel, Word
and my trusty Sequence table of integers, I successfully tested this to
five thousand parameters:

CREATE PROCEDURE TestProc (
a0001 INTEGER = 1,
a0002 INTEGER = 2,
a0003 INTEGER = 3,
...
a5000 INTEGER = 5000
)
AS
SELECT seq
FROM [Sequence]
WHERE seq IN (a0001, a0002, a0003, ..., a5000);

Above this I started to encounter Jet errors that appear related to
other limits: query too complex, system resources, etc.

I'm pretty sure, though, that 5000 parameters is enough for all
practical purposes.

Jamie.

--