Is Null/Is Not Null Parameter? (1 Viewer)

[mrabrams2]

Is it possible to have as the criteria for a field in a query:

[Is Null or Is Not Null?]

Where the parameter box pops up, the user enters Is Null, and the query runs using Is Null as the criteria for the specified field?

(It didn't work using the above example!)

Thanks!!
Michael

 

[Pat Hartman]

Your where clause should be:

Where YourField = [Enter Value] Or [Enter Value] Is Null;

 

[mrabrams2]

THANKS!!!!!!!!!!!!!

I have a field in a table called MyField. For some records this field is populated, for some records it is not populated.

I want to run a query that says:
Give me all records where MyField Is Null
or
Give me all records where MyField Is Not Null.

I would like to place a parameter where the user can choose Is Null, or Is Not Null. (The actual value in the field is irrelevant for this particular query.

The line:
Where YourField = [Enter Value] Or [Enter Value] Is Null
gives me either specific records based on the value in MyField, or ALL records whether MyField is null or not.

Sorry if I didn't explain it right the first time.

Thanks Pat!

Michael

 

[Jon K]

Set these in a column in the query grid:-

Field: IIf([Is Null or Is Not Null?]="Is Null",[MyField] Is Null,IIf([Is Null or Is Not Null?]="Is Not Null",[MyField] Is Not Null,False))

Show: uncheck

Criteria: <>False

 

[mrabrams2]

All I can say (after shaking my head) is THANK YOU very much.

It works wonderfully - now I need to study it and learn.

JonK - Thanks again.

Michael

 

[Pat Hartman]

Here is a simplified version:

Where IIf([Enter Is Null or Is Not Null] = "Is Null", IIf(IsNull(YourField), True, False), IIf(IsNull(YourField), False, True));

 

[mrabrams2]

Simple !?

Thanks - I will break that one down too so I can understand it and
use it properly in the future.

Pat & Jon - Thanks for sharing your time & knowledge.

Michael

 

[Lila]

13 years later this seems to be the only solution to this particular problem on the big wide web. Worked like a charm for me. Thank you so much!