I have a query that I control with some filtering options using a form. One of those filters is a medical code search. Sometime this field has a value and sometimes it is null. I want to be able to search for a partial string of data as well. The code I am trying is:
Expr9: ([tblQA].[Charges Entered]) Like Nz("*" & [Forms]![MainMenu]![btnManagersMenu].[Form]![txtChargeCode] & "*","*")
What I would like to have happen is if I enter some value in txtChargeCode that it will return any record with that string. That part works. The problem comes in when txtChargeCode has no value. If the record is null for Charges Entered then those records are filtered out of the results. I still see records that have a value. I want any null values for Charges Entered in the records to appear in the results, if txtChargeCode is left blank. Can anyone help me with whats going on here? I've read the Like and Nz pages so many times my eyes have crossed.
Your current criteria will never be null because it will always include the string "**".
Instead, we will use Nz to convert null values in [Charges Entered] into empty strings (""). Now that all items in [Charges Entered] are considered to be strings in the criteria, we can compare them with the LIKE operator.
The expression below will return all records (including nulls) when txtChargeCode is blank.
Nz([tblQA].[Charges Entered], "") Like "*" & [Forms]![MainMenu]![btnManagersMenu].[Form]![txtChargeCode] & "*"
You could sort [Charges Entered] so nulls are displayed at the top of the results to make it easier to spot nulls when txtChargeCode is empty.