MS Access - Using Form Inputs to Filter on Multiple Criteria in a Query

Question

In MS Access, I want to use a Form to drive the multiple filtering criteria for a query. In each of the combo boxes, I added null values for users to leave blank as not all criteria need a specified value

Form

Currently, I am able to filter on the first field [Type of Agreement], but cannot get the Sponsor field to work. This prevents me from even trying to filter based on the other fields. As a starting point, this is what I have:

SELECT TestInPut.[Type of Agreement], TestInPut.Sponsor, TestInPut.[Proposal Title], TestInPut.Div,  TestInPut.[Award Status], 
FROM TestInPut
WHERE ((TestInput.[Type of Agreement])=Forms!Console!SelectType) Or ((Forms!Console!SelectType) Is Null);

I tried the following solution, but my code did not apply if I chose a value in the Sponsor combo box: https://stackoverflow.com/a/19568169

How can I develop my code properly?

Thanks.

John F John F · Accepted Answer · 2018-07-03T02:26:10

The idea for this kind of filter is to convert the Null value in the control to SQL that means no conditions on this field. To do this we use the Nz function and the LIKE keyword.

The WHERE part should read:

WHERE TestInput.[Type of Agreement] LIKE Nz("'" & Forms["Console"].SelectType & "'","'*'") AND
      TestInPut.Sponsor LIKE Nz("'" & Forms["Console"].Sponsor & "'","'*'") AND ...

and so on for each field you want to filter on. Now, if the combobox is Null, it substitutes LIKE '*' which returns everything for that field. If it's populated with a value, it substitutes LIKE 'Grant' for example. No wildcards (*, ?) mean it's effectively asking for it to be equal.

EDIT: Sorry a few errors in that. It should be:

WHERE TestInput.[Type of Agreement] LIKE Nz([Forms]![Console]![SelectType],"*") AND
          TestInPut.Sponsor LIKE Nz([Forms]![Console]![Sponsor],"*")

MS Access - Using Form Inputs to Filter on Multiple Criteria in a Query

1 Answers