Filtering Yes/No Fields with functions in a query

[LonghronJ]

I have a form that would allow the user to click numerous checkboxes to further filter the data. Initially, when the datatype is Yes/No, and the checkbox is not selected, the filter considers the field as "No", which is not the intent. I want the checkbox selected as a further filter of "Yes", so I now changed the datatype to Short text with data filled with "Yes" and blanks. So now, when the user selects the checkbox, I will assign a global variable to "Yes". Now, how do I write it in the criteria in the query that if the function (from the return of the global variable) is null, then treat it as null, so the data is not filtered to Yes"? I have tried "Like myFunction() or is null" in the criteria and various similar syntax but it doesn't seem to filter correctly. What is the exact syntax I should use?

[June7]

I NEVER use dynamic parameterized queries. I prefer http://www.allenbrowne.com/ser-62.html

[LonghronJ]

In this form, there are actually many other fields the user can select to filter the data, such as drop-down boxes and text boxes. When using the syntax listed in the link you've provided for the various parameters, they work fine, but for some reason, I cannot get it to work with these check boxes. For this particular field, it is not recognizing blank as null but as " ", so when I make the function return " " and run the query, the results include all "Yes" vs all records. I don't understand it.

[June7]

For a dynamic parameterized query, make sure every record has a value (Yes or No). Nulls will be ignored by text parameter. Otherwise, options for handling the Null:

1. construct field in query with Nz([fieldname]) that provides a value (can be "" or No) for the Null and apply filter criteria to that field

2. use LIKE '*" & [input parameter] & "*" Or Is Null in the criteria under the natural field

For the VBA method, if checkbox is False then don't include that field in the criteria construction.

Post your relevant code.

[orange]

An apparently "empty field" could be valued
"" -zero length string,
" " - space, or
NULL

You should check for all possibilities

[LonghronJ]

I intentionally replaced all NO with empty, so that when the user does not click on the checkbox, it does not mean the user wants NO as a parameter, but simply means that he/she does not want to filter the data with YES. I don't know if I'm making sense.

[orange]

I have no idea what that last post meant????

[June7]

Okay, but if you are using the VBA method, you manage that in the code. If the checkbox is No, don't include that field in the criteria construction.

If you are using dynamic parameterized query, then follow suggestion in post4.

[LonghronJ]

OK. I was able to solve my problem. Originally, I thought the check boxes will either give you a (-1) when selected or (0) deselected, so the query I had design will either return records with -1 or 0, but apparently you can make the value of the check box to be null. So, I went back and change the data type to Yes/No and I initialize the check box to null when it is first open, and I put in the criteria of the query to

Like [Forms]![frmName]![checkBox1] & "*" Or Is Null

So now, if the user does not wish to filter the result by selecting the check boxes, the result will show all data. Hopefully this will help others down the road.