Do Not Display Null Records - Microsoft Access Forum


	Back to Access Forum Comments List

Do Not Display Null Records

Dan Jackson

3 years ago

Hi Guys, Hopefully a quick one.

I have a continuous form with combo's in the header to filter results.
Combo is called Combo_SearchChaser

In The Query, it looks like
[Forms]![AdminF]![Combo_SearchChaser] Or Is Null
This is so i can see all the records including the ones with nothing in the case section

However, when someone does a filter using the combo. It shows the result AND Null Records. I want to exclude null records when searched.

I tried this in the criteria but I get Parameter dialog. What am i missing? Thank You

IIF ([Forms]![AdminF]![Combo_SearchChaser] = Null, [Forms]![AdminF]![Combo_SearchChaser] Or Is Null, [Forms]![AdminF]![Combo_SearchChaser])

	Kevin Robertson @Reply 3 years ago
Try this Dan: `[Forms]![AdminF]![Combo_SearchChaser] And Not Is Null`

Kevin Yip @Reply

3 years ago

Hi Dan, you were almost right in your first try above.

If your combo box is null, you want this query to take effect:

    SELECT * FROM MyTable WHERE IsNull([MyCombo])

which will become:

   SELECT * FROM MyTable WHERE True

which will naturally show all records.

If your combo box is not null, you want this query to take effect:

   SELECT * FROM MyTable WHERE MyField = [MyCombo]

So to take both cases into account, you simply use OR on the two cases:

   SELECT * FROM MyTable WHERE IsNull([MyCombo]) OR MyField = [MyCombo]

It may be easier to type the SQL statement directly, as shown above.  It is actually trickier to do it on the graphical query designer.  See the picture below showing how it should look on the query designer.

	Kevin Yip @Reply 3 years ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen