Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Maximun number of 'or' criteria in query (2003)

    One of our users needs to create a select query with 140 "or" criteria in it. This database contains work order numbers. She needs to pull 145 different work order numbers from the db. She is doing a simple select query and typing each work order number in the criteria with "or"... and it works fine till she enters the 97th criteria. How can we write this query so we can have 145 different criteria items to pull using "or"?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximun number of 'or' criteria in query (2003)

    If the numbers are following you could use the Between statement.
    An other possibility would be the Instr function.
    Or you could use some vba to build a function to check if the order number exist in a table.
    Sorry, but 145 or's sound a little bit crazy.
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximun number of 'or' criteria in query (2003)

    Thanks for the suggestion. "Crazy"... that's just about what I thought when this user called me and asked how to make this work. I think maybe grouping them into several between statements might be a way to go. I'm going to try to get her to give me a copy of the db to test with. Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Maximun number of 'or' criteria in query (2003)

    Yet another possibility is to use a condition of the form

    In(12, 35, 67, 128, ...)

    If I need something like that, I present the user with a multi-select list box, and construct the condition in VBA. <post#=465456>post 465456</post#> gives an example of such code.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Max # in query - solved

    Well, we found a way to do this..in a much less complicated manner than the user was suggesting. We put all the ID's in a table in the database and used a subquery for the where clause.

    SELECT tbl_MASTER.Merchant, tbl_MASTER.CH_Name, tbl_INDEX.Alct_TransAmt, tbl_INDEX.Index_ID, tbl_INDEX.PTC, tbl_INDEX.PRJ_ID, tbl_INDEX.WO_ID, tbl_INDEX.CC_ID, tbl_MASTER.Actual_Reason
    FROM tbl_INDEX INNER JOIN tbl_MASTER ON tbl_INDEX.Index_ID=tbl_MASTER.Index_ID
    WHERE (((tbl_INDEX.WO_ID) in (select * from Tbl_Of_IDs)))

    Thanks for the suggestions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •