Databases
StackOverflow2013
Postsdbo
POIs there a preferred way to create dynamic, form-based queries in MS Access?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there a preferred way to create dynamic, form-based queries in MS Access?
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPSUlion01
    1. USPSUlion01
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs there a preferred way to create dynamic, form-based queries in MS Access?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs there a preferred way to create dynamic, form-based queries in MS Access?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIs there a preferred way to create dynamic, form-based queries in MS Access?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload