MSACCESS returning NULL values where textbox = “blank”

I have a search query that returns values from textboxes. I want to add the functionality to return all rows where Comments is null or empty.

I have tried editing the SQL to accept “blank” in the textbox and return all the rows where comments is null. I just added the OR to the original portion of the code.

(((IIf(Nz([Forms]![Home]![CHOOSE_COMMENTS],"")="","",[WCL_DATABASE].[COMMENTS] Like [Forms]![Home]![CHOOSE_COMMENTS]))<>False) 

or

((IIf(Nz([Forms]![Home]![CHOOSE_COMMENTS],"")="Blank","",[WCL_DATABASE].[COMMENTS] Like ""))<>False) )`

When I search “blank”, I receive no results.

Answer

Null and an empty string ("") are two different values.

I’m guessing you’ll want to use something like this:

select * from YourTable where 
([Forms]![Home]![CHOOSE_COMMENTS] = "Blank" and [WCL_DATABASE].[COMMENTS] is null) or
([Forms]![Home]![CHOOSE_COMMENTS] is null) or
([WCL_DATABASE].[COMMENTS] like [Forms]![Home]![CHOOSE_COMMENTS])

This means:

• If the user types “Blank” then records with no comments are returned.
• If the user leaves the comments field blank, then all records are returned (no filter).
• Else the comments are wildcard matched with the user entry.