Query Not Returning Null Fields

[mgmirvine]

First, thank you for reading my post. I'm familiar with Access, but definitely not a pro. So, I'd like to apply criteria to a query, such that (1) all records are returned if no input provided, or (2) only the records matching input. My expression looks like this:



Like "*" & [Forms]![CodeSearchForm]![Code] & "*"

So when a user types some info into the CodeSearchForm, the query should return the records that match that code. That works fine, but problem is when user leaves the input blank, it only returns not null records.

I've searched and searched for a solution this this. Modifying the expression with "OR Is Null" won't work, because then when the user does input criteria, it will return the codes that match AND the null records.

Any help you can provide is greatly appreciated. Thank you

[hertfordkc]

field as "". I had to resort to that recently, but there wasn't a logical reason not to do it. If you have a real distinction between null and "", then it may not work for you.
To further clarify, I placed a calculated variable in a field in the query design:
A:nz([A],"")

[mgmirvine]

hertfordkc, thank you so much for this tip! It totally worked.