Finding Null Values

[Ella]

I am trying to find null values using a checkbox in my form. If the tick box
is True then all records with a null value in the category field are
displayed. If the checkbox is false all records are shown.
I tried the iif below but I just can’t get it to work.

IIf([Forms]![frmReview]![Category]= true,Is Null,"*")

I am a novus with access so I hope someone can help.

Ella

 

[Allen Browne]

You can't just type that into the criteria and have it work.

Switch the query to SQL View (View menu.)
Locate the WHERE clause

WHERE ((([Forms]![frmReview]![Category]= False)
OR ([PutYourFieldNameHere] Is Null))

The important thing is that the expression evalutes to TRUE all the time if
the check box on the form is unchecked, and so all records are returned. If
the check box is not true, then the expression evaluates to TRUE only for
the records that are null.

 

[Ella]

Allen,
Your suggestion worked really well, thank you!
In working further with the database, I find that I need to increase the
filtering.
How can I search using the checkbox [Category] to show only null or all
records and a textbox to show a specific Category or if the textbox is null
all records.
To explain:
(1) Chechbox False & textbox null = all records
(2) Chechbox True & textbox null = all records with a null Category
(3) Chechbox True & textbox has a value = all records with a null Category
and records with the specified Category
(4) Chechbox False & textbox has a value = only records with the specified
Category

I have tried this SQL but can’t get all the results. I can only get a result
for (3).

WHERE (([Forms]![frmReview]![Category])=False) OR (([CategoryAssigned] Is
Null)) OR (((qReviewItems.
CategoryAssigned)=[Forms]![frmReview]![CategoryType])) OR
((([Forms]![frmReview]![CategoryType]) Is Null));

I hope you can help?
Ella

You can't just type that into the criteria and have it work.

Switch the query to SQL View (View menu.)
Locate the WHERE clause

WHERE ((([Forms]![frmReview]![Category]= False)
OR ([PutYourFieldNameHere] Is Null))

The important thing is that the expression evalutes to TRUE all the time if
the check box on the form is unchecked, and so all records are returned. If
the check box is not true, then the expression evaluates to TRUE only for
the records that are null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to find null values using a checkbox in my form. If the tick
box
is True then all records with a null value in the category field are
displayed. If the checkbox is false all records are shown.
I tried the iif below but I just can’t get it to work.

IIf([Forms]![frmReview]![Category]= true,Is Null,"*")

I am a novus with access so I hope someone can help.

Ella

 

[Allen Browne]

Ella, is it possible to craft a convoluted WHERE clause to do what you ask,
but it gets messy and inefficient. A better solution is to build the filter
string dynamically, from only those boxes where the user entered something.

Download the sample database from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Spend some time studying how it works, and you will be able to build any
filter you need for your forms and reports.

This is something you need to do so often that it is really worth the
trouble to get to know the technique.