Search code examples
sqlms-accessvbams-access-2007

Criteria to return all records if selection from form is both

I'm trying to pass multiple query criteria from form controls. I'm using the following type of syntax for the criteria.

IIf([Forms]![ReportsForm]![cmbLvl]="ALL", "*", [Forms]![ReportsForm]![cmbLvl])

It works for the else clause. But it does not work for the ALL Scenario. Is there an easy way to make this work.

What I am trying to do is to return all records and not just one type. For example. On a table I want to be able to search for both Male and Female. Not just Male or vice versa. Not just female only too. But both.

I also tried Like Nz([Forms]![ReportsForm]![cmbLvl],"*") . That did not work also.

Solution

  • Use a parameter to return all records if Null shows how you can use a Null field value to lift that field's filter. To adapt this, you could use: [Forms]![ReportsForm]![cmbLvl]="All" OR [Forms]![ReportsForm]![cmbLvl]

    If you need to show "ALL" as a combobox choice rather than just leaving it blank, consult Adding "All" to a listbox or combobox's part about creating a union query including a null value listed as "All".