Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Extra Select Query Criteria Parameters

Discussion in 'Information Technology' started by CharlesGuzman@gmail.com, Jul 28, 2006.

  1. CharlesGuzman@gmail.com

    CharlesGuzman@gmail.com
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. G. Vaught

    G. Vaught
    Expand Collapse
    Guest

    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.

    <CharlesGuzman@gmail.com> wrote in message
    news:1153516383.584414.327250@i3g2000cwc.googlegroups.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
    >
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 21 Jul 2006 19:52:21 -0600, "G. Vaught" <glvaught@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]
     
  5. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    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.

    --
     

Share This Page