Return value and null from query

[bschulze]

I have a query that is evaluating criteria and returning values. The evaluation is working properly but I'm having a hard time getting the results I desire. The field in the query is a Text field. I usually get an complication error unless I use the Like clause. The list of criteria I've tried never returns the null values, only the items that contain data if at all. When I drop the is null from the true statement and put it on a separate line it works, problem is as expected in that case, it picks up nulls for both the true and false conditions.

Query Criteria:
IIF(DMax("P","tblP")=[Forms]![frm1].[cbo1],DMax("P","tblP") or is null,[Forms]![frm1].[cbo1])

Simplified:
IIF(a=b,a or is null,b)


What I've tried so far:
IIF(a=b,a or is null,b)
IIF(a=b,a and is null,b)
Like IIF(a=b,a or is null,b)
Like IIF(a=b,a and is null,b)
IIF(a=b,Like a or is null,b)
IIF(a=b,Like a and is null,b)



Any thoughts would be greatly appreciated, I think I'm just overlooking something easy.

 

[bschulze]

Your first suggestion will work perfect for me, very creative with the NZ(). Since I'm loading a subform with this query I want to stay away from UNIONS if possible, but can see that would also fit the bill. Thanks again.

 

[xenou]

NP. Confused me at first but glad we sorted it out.