Is Null

[perryc]

How to filter a Query in Access for Is Null (not "Is Null")??

NZ([Value1], Is Null)

or

IIF(IsNull(Value1] = True, Is Null, 0)


Basically, the above two codes generate no results. If I add a "Is Null", it yields no result either because the Field is looking for "Is Null" which is not what the field contains. I want to show everything that is truly Is Null like below but don't know how to code this!

e.g.

[June7]

SELECT * FROM table WHERE RefType Is Null;

What you show in image looks correct. If this returns no records then none are Null.

Post example data and full SQL statement attempted.

[perryc]

I was looking for a criteria statement within the query, not from a code executed from the form to this.

i.e. How to fix this:

IIF(IsNull(Value1] = True, Is Null, 0) in order for it to generate the result Is Null in the criteria section in the Query??

[June7]

I did not show code from a form. I show the SQL statement you can see in query SQLView. Switch to SQLView and copy/paste the SQL statement. Provide sample data.

As I said, what you show looks correct if you want to retrieve records where field is null.

[perryc]

I see. Sorry... I will give it a try.

[perryc]

June7, very sorry for these novice questions. So, I think SELECT * FROM table WHERE RefType Is Null will simply put a Is Null in the criteria of the query. I know that, but what really need to happen, is I am trying to pull some values from a form, but if the value on the form field is Null, then, I want to query all the Nulls, otherwise, I want to query the value on that field.

Field1 on the form: AAA or BBB

In the Query, If it is not AAA or BBB, then show all the Nulls. Therefore,

IIF(IsNull([Field1] = True, Is Null, Field1)

Sorry for the confusion.

[davegri]

If you want the argument to be the words "is Null", then

IIF(IsNull([Field1] = True, "Is Null", Field1)

If you want the argument to be null, then

IIF(IsNull([Field1] = True,Null, Field1)

[June7]

You want conditional criteria based on what is in a control on form?

Unfortunately, operators (= <> LIKE, etc) cannot be dynamic. Only way I can see to do in query what I think you want is to calculate substitute value for the nulls.

WHERE Nz(fieldname,"zzz") = Nz(Forms!formname!controlname, "zzz")


I really don't like dynamic parameterized queries. I prefer VBA to set filter criteria. Review http://allenbrowne.com/ser-62.html

[perryc]

Ty all again!!