Query Referencing Multiple Form Fields

[houseofturner]

I want to run a query that uses criteria specified in a series of fields on a form. However, not all the fields on the form will have data stored in each time the query is run.

So for example, the query has the fields Manufacturer, Model, Variant. The form has has the same fields.

If someone has entered "BMW" in the manufacturer field on the form, the query should only return BMW records.

But I need to put something in the criteria to say that if (for example) the manufacturer field on the form is empty then no criteria is applied and it should return all records.

I tried putting the following in the criteria field but it works if there is a value on the form but not when the form field is empty.

IIf(Not ([FormName].[Manufacturer])=IsNull([Forms]![FormName]![Manufacturer Filter]),[Forms]![FormName]![Manufacturer Filter])

I also tried

IIf(Not ([FormName].[Manufacturer])=IsNull([Forms]![FormName]![Manufacturer Filter]),[Forms]![FormName]![Manufacturer Filter],"Audi") to see if it was a lack of an alternative criteria but that didn't work either.

 

[arnelgp]

IIf(IsNull ([Form]![FormName]![Manufacturer]), [Manufacturer], [Forms]![FormName]![Manufacturer])

 

[houseofturner]

Hi, that's great. Could I wildcard either side of the manufacturer field. Tried but maybe my syntax is wrong. Some of the drop downs of a similar nature may just say BMW as part of the string.

Thanks!

 

[arnelgp]

[Manufacturer] Like '*' & IIf(IsNull ([Form]![FormName]![Manufacturer]), [Manufacturer], [Forms]![FormName]![Manufacturer]) & '*'