Search code examples
databasems-accessfilterms-access-forms

Apply query criteria based on parameters

I need to run a query in a MS Access Database providing some parameters from a form. Imagine the next example:

I have a form which contains:

  • CheckBox1 and Text1
  • CheckBox2 and Text2
  • Button (to run query)

Now imagine a query with two fields: ID, NAME.

I want to filter ID by Text1 only when CheckBox1 is enabled. If not, I want the query not to filter ID in any way (as if the 'query' input was empty).

In the same way, I want to filter NAME by Text2 only when CheckBox2 is enabled. If not, I want the query not to filter NAME in any way (just like ID before).

I've tried so many things for a couple of days and have sniffed tons of internet pages and still don't come up with a solution.

Solution

  • You can use a SQL query such as the following:

    select * from YourTable t
where
    ([Forms]![YourForm]![CheckBox1] = False or t.ID   = [Forms]![YourForm]![Text1]) and
    ([Forms]![YourForm]![CheckBox2] = False or t.NAME = [Forms]![YourForm]![Text2])

    (Change YourTable to the name of your table and YourForm to the name of your form; t is merely an alias so that you only have to change the table name in one place in the code).