Can I make a form to input criteria for many fields in a query

[dougmn]

I want to build a query that will have criteria in several fields, maybe 7 or so different ones. This query would be the basis for reports. Is there a way to use a form to input all the different criteria in one screen, and pass them to the query via variables? Maybe the form could assign the values of the various controls to variables, then these could be accessed in SQL commands. That’s my idea anyway, but I don’t know how to do it.

The reason I want to do this is that sometimes it’s useful to see what the results are, then just change the criterion for one of the fields and leave everything else alone. It would be harder (and less convenient) to do so without making mistakes if I just rely on the dialogues that would pop up when running the query.

[Ratslinger]

Hello,

This is power filtering. Use of a one record table which contains the variable(s) to be used in your query. There are numerous examples throughout this forum. Most are for filtering records for display on a form. In your case this same technique can be used but you really don’t even need a form. Just change the data in the Filter table record and see results in your query.

In my answer on this post → Need to create a form to enter a record ID to delete there is a link to ‘Filter/Searching with Forms’ which goes into more details about this. This answer also has an example. This example is the basis of what you want.

Another sample is in my answer here → Need to Filter a table with two fields for key words

Edit:

@dougmn You are nearly on the right track. The filter table, which only uses one record, needs a key field (1st field - Integer - NO auto increment) and a field for each of the variables you may have. It doesn’t need to be a duplication of the table record you are querying. It doesn’t even need to have the same field names. It is simple a “holding” area to use in your SQL.

Have attached a sample – TableFilter.odb

Open the table TableFilter. Change values in Color or Wheels fields of record (be sure to save record) then run the query SampleFilterSelect. Change TableFilter value(s) or delete a value & re-run query. Results will change as data in filter table is changed.

Note: You can, as originally asked, create a form which would fill in parameters & even execute the query but it would require macro code specifically to your design. In this case the TableFilter would not even be used.

[dougmn]

Thank you for your answer. I’ve been playing around with it. If I understand correctly, I create a table with the same structure as the table I want to query, and the data I want to use as criteria goes into the single record in that table. Then in the query, I link the two tables, and I get just the records that match what I put in the 1-record table, without the need to put anything in the criteria row of the query design. Am I understanding it correctly?

[dougmn]

@ ratslinger Thank you for the help, and the elegantly simple example. I especially like that it works if the user leaves a filter field blank. I wanted to allow for that possibility. I was thinking of giving the fields a default value of “*”, and using a “like” statement in the query. But I like what you have done better.