Ignoring Null Fields In Form When Running Query

[stevefutcher]

I am trying to write a query that searches a table with address data based on criteria input using a form.

The trouble is that if the field in the form is null the criteria in the query is "Is Null".

I want to return all values for a field if the form in the form is null, i.e. the equivalent of entering no criteria in the query.

I have tried IIF(IsNull([AddressTable]![Fieldname])=True,"",[AddressTable]![Fieldname]) but this doesn't seem to work.

Any ideas?

 

[Remou]

It would probably be best to build the SQL string programmatically. This can then be used with a new or an existing query.

 

[PHV]

In the criteria cell:
=[FORMS]![main form]![text box] OR [FORMS]![main form]![text box] Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[stevefutcher]

Works a treat. Thanks very much.

Steve