Query Criteria Like or is null

[jlgray0127]

Hello.


I am struggling to find the fix for this. I found it once upon a time, but am not asking the correct question.

I have an unbound, main form with several unbound combo boxes which use other tables for sources and a subform whose source is a query.

Once I make a selection I want to filter the results in the sub query.

For years, I had done this using:

Field
Recovery Group

Criteria
Like "*" & [Forms]![MTP_Wk_Main_Snap]![RecovGPComb] & "*" Or Is Null

This almost works great. If I do not make a selection, the query only looks at the combo boxes with criteria selected.
If I select a value, it will provide the records that match.

THE PROBLEM:
Not all records have a value in this field. So, if I select Recovery group 1, I get Recovery group 1 and any records which are null.
I can not seem to put my finger on how to rewrite this to show me the records with a matching or almost matching value ad not show me the null fields Thoughts on what I cam missing here?

If I select a value in the combo box, I want only the records that match. If I leave the combo box as null, I want all records.

Hopefully this is clear and simple enough. I am sure the answer will be blatantly obvious if someone would be so kind as to share their thoughts. Thank you.

[June7]

Criteria is in query design? I don't normally use dynamic parameterized queries. I use VBA to build filter criteria and apply to form or report. Review: http://allenbrowne.com/ser-62.html

Otherwise, try:

Nz([Recovery Group], "") LIKE "*" & Nz([Forms]![MTP_Wk_Main_Snap]![RecovGPComb],"") & "*"

Is Recovery Group a number type field? How many groups are there? LIKE operator does not work properly with number values.

[CJ_London]

What about

Like "*" & [Forms]![MTP_Wk_Main_Snap]![RecovGPComb] & "*" Or [Forms]![MTP_Wk_Main_Snap]![RecovGPComb] Is Null

[Micron]

To explain the answers so as to not repeat that sort of thinking in the future, each part of this expression needs to be able to stand on its own:
Like "*" & [Forms]![MTP_Wk_Main_Snap]![RecovGPComb] & "*" Or Is Null

so Like "*" & [Forms]![MTP_Wk_Main_Snap]![RecovGPComb] & "*"
can stand alone but

Or Is Null
makes no sense in terms of code.