Is Null Query Criteria correction

The two different examples of the query in design view illustrate what I said about Access moving things around.  The underlying logic is the same, but Access expresses it in a different way.  When you are restricting the query on only one column this is not a problem.  When you restrict the query on multiple columns in this way, however, the way Access moves things around can change the simple logic of the query written in SQL view to much more complex logic, sometimes resulting in the query being unable to open.  

This is why I stress that such queries should only be written and saved in SQL view.  Your form is a good example of this.  If the query were to be created and saved in design view the logic of the WHERE clause into which Access translates the visual design interface would be very complex.

The logic for restricting the query on multiple columns, as you are attempting, is actually very simple:

WHERE (SomeColumn = <some parameter>

OR <some parameter> IS NULL)

AND (SomeOtherColumn = <some other parameter>

OR <some other parameter> IS NULL)

AND etc

Each OR operation is enclosed in parentheses to force it to evaluate independently.  These parenthesized expressions are then tacked together with AND operations.  The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter, or if the parameter is left empty (NULL),  for every row.  By virtue of the AND operations  the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

The following is an example of such a query from one of my online demo files:

SELECT Counties.County, Districts.District, Parishes.Parish,

Locations.Location, Parishes.ParishID  

FROM ((Counties INNER JOIN Districts ON Counties.CountyID = Districts.CountyID)  

INNER JOIN Parishes ON Districts.DistrictID = Parishes.DistrictID)  

INNER JOIN Locations ON Parishes.ParishID = Locations.ParishID  

WHERE (Counties.CountyID=[Forms]![frmDrillDown_Simple]![cboGotoCounty]

OR [Forms]![frmDrillDown_Simple]![cboGotoCounty] IS NULL)

AND (Districts.DistrictID=[Forms]![frmDrillDown_Simple]![cboGotoDistrict]

OR [Forms]![frmDrillDown_Simple]![cboGotoDistrict] IS NULL)

AND (Parishes.ParishID=[Forms]![frmDrillDown_Simple]![cboGotoParish]

OR [Forms]![frmDrillDown_Simple]![cboGotoParish] IS NULL);

The above query is from the ComboDemo file which you'll find in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In the same OneDrive folder the MultiSelect demo file illustrates the use of a query restricted in this way as a report's RecordSource, along with other ways of restricting a report's output by criteria entered in a dialogue form.

To do this in design view you would simply enter the expression:

LIKE "*" & [Forms]![FrmSearch]![PCategory] & "*" OR  NZ([Forms]![FrmSearch]![PCategory],"") = ""

in the criteria row of the PCategory column.

However, I would recommend against entering expressions like this which include a Boolean operation in design view.  If you open the query again in design view you'll find that Access has moved things around.  This obscures the logic, and in queries where a number of similar expressions are combined in Boolean AND operations, can even make the query too complex to open.

The logic of the expression is very simple:

LIKE "*" & [Forms]![FrmSearch]![PCategory] & "*" will evaluate to TRUE for any row where the value in the PCategory column matches the value entered in the control in the search form, or includes the value as a substring. Those rows will therefore be returned.

NZ([Forms]![FrmSearch]![PCategory],"") = "" will evaluate to TRUE for any row, regardless of the value of the PCategory column, if the control in the search form is NULL or contains a zero-length string.  Consequently all rows will be returned.