Results 1 to 3 of 3
  1. #1
    bomich is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    10

    Criteria string too long

    I have a form in which the user sets criteria into text boxes, I want to display a subform/datasheet below showing the records which meet the criteria given in the text boxes.



    There are 8 criteria boxes and 2 "date between" criteria so 14 text boxes which can be set.

    I want the user to be able to input values into any, all or none of the criteria to show the relevant records in the datasheet (so if any criteria are left empty then all records should be returned for that field in the table).

    The way I have done it so far is with a stupidly long WHERE clause which doubles every time I add a new criteria (since it has to list every possible combination of all 14 criteria either having a value or "is null").
    Like I said; 14 criteria boxes. Ridiculusly long!

    It was working fine until I got to the 8th criteria box when I got an error message basicly telling me that my SQL string is way too long.

    Is there a better way to set multiple criteria for a form/datasheet? Or a way of allowing a longer SQL string?

    Any help will be greatly appreciated!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is this SQL you're building in code or using a saved query? If in code, you just skip controls the user didn't fill in.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    bomich is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    10
    I'm still quite new to this. I think its a saved query.
    By code do you mean VBA code which creates the SQL..? I have seen somewhere where you call SQL from inside VB but I don't know how you use that to alter the SQL for an already built query..? Could you explain what you mean/how you do it?

    I think I might be in the wrong forum thread also?! I just saw SQL and thought it was to do with my issue but SQL server is different I think..?!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. find all text string in Criteria at once
    By Jerseynjphillypa in forum Queries
    Replies: 3
    Last Post: 05-15-2012, 12:12 PM
  2. Multiple Fields in Criteria - String
    By alsoto in forum Forms
    Replies: 3
    Last Post: 08-24-2011, 12:23 PM
  3. Multiple Criteria in a string
    By cksm4 in forum Programming
    Replies: 3
    Last Post: 08-04-2010, 11:54 AM
  4. Replies: 4
    Last Post: 07-28-2010, 11:25 AM
  5. How to Concatenate String Criteria
    By ColPat in forum Programming
    Replies: 2
    Last Post: 06-26-2010, 08:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums