Query results no record because another criteria field is blank

[ITguy1981]

I currently have a query that I use to search records by firstname, midname, lastname. The criteria is entered on another form. The query pulls the data from the form to fill the criteria. For example, the query has the field "Firstname" and the criteria is - Like "*" & [Forms]![Formname]![Textboxname] & "*". This similar critieria is also used for the fields "MidName" and "LastName" in the query. It works fine as long as records contain data in those fields. My problem occurs when I have a name in a record that does not have a middle name. If I have a record with say the John Robert Smith and I just search for john as the first name it finds the record. If the name in the record was just John Smith the query finds zero results because the "MidName" field has no entry. How can I fix this?

 

[Beetle]

Change your criteria to;

Is Null Or Like "*" & [Forms]![Formname]![Textboxname] & "*"

 

[ITguy1981]

Thank you soooo much. That worked. I was trying different If functions and everything else. I guess sometimes we overlook the simplest solution. Again, thank you.

 

[Beetle]

Happy to help.