Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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.

    --
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page