Again, query where dealing with empty fields

[Geirr]

Hi all.
I'm sorry if I am bringing up probably an old and answered issue, but I just can't get things to work properly, and I cannot find a solution while trying to search for it.

I have a table with 5 column. For the sake of argument, - Field01 - Field05, all text fields. I do have a form with format set to Continuous Forms, and the record set to a query which held all fields. Secondly, in the from header - I have a three textbox (txtCriteria1 - txtCriteria3) which are used as a search criteria for the form, Field01, field02 and field03 where in the query I am using the standard (but not the best practice) criteria: Like "*" & [Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria1] & "*" (and ...[txtCriteria2], ...[txtCriteria3] for Field02 and Field03)
This works as a charm, but on records which does not have data in the field Field01 -02 and -03, I got all the records matching the criteria and also the records with empty value.

How can I show every records unfiltered, but only those who match the filter when filtered?
I found a hint in this forum from 2007; - iif(Forms!myform!SearchBox = "",True,mytable.TextField is like Forms!myform!Searchbox & "*") - but I just don't understand where and how to us it in the record source query. I'm just got totally blinded on this one.

I'm thankful for all help!
Brg, Geirr.

 

[theDBguy]

Unfortunately, the "Like" operator ignore null values. To include them, you'll have to use something like:

Like "something" AND FieldName Is Not Null

 

[arnelgp]

you can also try (for Field01):

 Like "*" & Nz([Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria1], [Field01]) & "*"

put the same on the other 2 fields.