Handling null values in Expression builder

[Ronaldo9]

In Expression Builder, I have the query(in the Criteria row) set to run based on values in different fields on a form. When one of the fields is empty however, the query will not return any records.

How can tell Access to ignore the fields when those fields on the form are empty and only look at fields that are not null.

Thank you in advance.

 

[Alfisdad]

This may be a bit long-winded, especially if you have a number of fields that you are filtering on, but you could do something like this for each table column that you're trying to filter with the value from a form field:

Let's say you have a table with a field OrderDate and the field that you want to filter this by is frmOrderSearch!txtOrderDate:

Create a new column on the query with Field:
ShowOrderDate: IIf(Isnull([frmOrderSearch]![txtOrderDate]),"Y", IIf(OrderDate = [frmOrderSearch]![txtOrderDate], "Y","N"))

then in the criteria row just put "Y"

Someone else might have a neater solution but this should work...

 

[Ronaldo9]

As you said, it's a bit of work, but it works great!!
Many Thanks!!