title: Is there a preferred way to create dynamic, form-based queries in MS Access?
body: <p>General question regarding the approach to take with a database I'm constructing.  I plan on having a few forms that will allow DB business users to enter criteria, restrict their search via radio buttons, etc. and then run a query based on their selections.  </p>
  
  <p>For simple queries, where there's one or two filters, it seems easy enough to associate the data field's criteria to the respective form element in the query design view, but this approach fails (and seems very hard to read/edit) when dealing with nested if statements (e.g. If A then x, if B then y, if C then z, Else q).</p>
  
  <p>I came across an article that described creating a separate table in the DB where one would store query names and corresponding SQL strings, which are created in VBA that runs after an onClick event, or some form-based trigger.  The logic for determining the query criteria is all in the VBA, and once the code executes the system is left with a clean SQL statment, which is stored in the aforementioned table and then used to execute the query.  I believe that each time the form is accessed/modified and a query requested, the SQL string in the table would be overwritten.</p>
  
  <p>As this is the first serious DB I've worked on, I'm looking for some guidance regarding the approach to take.  Is what I just described correct or a fairly standard/common way to handle the situation?  Are there any major concerns?  The one that immediately jumps to mind is what happens if two DB users are trying to run the same query, with different criteria, simultaneously?  It should be noted, my user base is small -- perhaps 5 users that will only be accessing the DB in passing, for ad-hoc reporting and such.</p>
  
  <p>Thanks in advance!</p>
  
  <p><a href="http://www.access-programmers.co.uk/forums/showthread.php?t=119990&amp;highlight=dynamic%20SQL" rel="nofollow">Edit:  Here's the forum post that I was referring to re: SQL strings in a separate table</a></p>
  
  <p>Edit 2: As a general example, let's say my DB has a table as follows:</p>
  
  <blockquote>
    <p>ID......Sale_Date......Category</p>
    
    <hr>
    
    <p>1.......  1/1/2013........Foo   <br>
    2.......  1/3/2013........Bar<br>
    3.......  1/1/2013........Bar<br>
    4.......  1/7/2013........Bar</p>
  </blockquote>
  
  <p>Now on my form, I'd have text boxes where the user can specify the date range, and this is filtered on the query as</p>
  
  <blockquote>
    <p>Between [Forms]![myFrm]![FromDate] And [Forms]![myFrm]![ToDate]</p>
  </blockquote>
  
  <p>which works just fine.</p>
  
  <p>As for the category, I'd like to have checkboxes or some other form element where the user can specify the categories for inclusion/exclusion.   This is where I ran into problems.  I tried:</p>
  
  <blockquote>
    <p>IIf([Forms]![myFrm]![Cat]=1,([tbl_data].[Category]) In ("Foo","Bar"),IIf([Forms]![myFrm]![Cat]=2,"Foo","Bar"))</p>
  </blockquote>
  
  <p>...with Cat=1 representing 'All' and 2 and 3 representing Foo and Bar respectively.  Access gives me an error that the query is too complex, but if I strip out the nested if (thus ignoring the option to search for both categories), it works.</p>
  
  <p>Now, clearly this is a greatly simplified example, but it got me thinking about how to handle the form-driven queries as the DB grows and more canned queries are baked in.  The thought with the VBA is that the code would run, evaluate the form data, and then construct the SQL as </p>
  
  <blockquote>
    <p>SELECT...<br>
    FROM...<br>
    WHERE IN("Foo", "Bar")</p>
  </blockquote>
  
  <p>Hopefully this helps clarify things a bit.  Please excuse my ignorance... I'm still learning a lot of this as I go along.  Thanks.</p>