CODEWITHSUNDEEP
databasems-accessfilterms-access-forms
Jordi Fuentes
Jordi Fuentes

Reputation: 47

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:

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.

Upvotes: 0

Views: 43

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

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).

Upvotes: 1

Related Questions