Null=NotNull

[Mac]

Dear All,

Apologies if I'm asking a question that has already been answered, but
I have a query that is tied to a form. The criteria of the form is tied
to a specific field. I'm trying to get the query to treat a null value
from the form field as not null (in effect giving me all possible
results). The criteria is as listed below:

IIf(IsNull([Forms]![Update Mailer Form]![Dept Selector]),Not
Null,([Forms]![Update Mailer Form]![Dept Selector]))

If I substitute the Not null for a specific value (numeric) then I get
all relevant results for that value, however the moment I put in either
an arithmetic operator (E.g. >0) or some kind of statement like Between
1 to 25, then the query runs, but return no results. I've also tried
wildcard characters (both "*" and "%") and get the messgae that the
expression is either incorrect or too comple to be evaluated. Can
anyone help?

 

[Allen Browne]

In query design view, choose SQL View from the View menu.

Locate the WHERE clause within the query statement.

Change it so it looks like this:
WHERE (([Forms]![Update Mailer Form]![Dept Selector] Is Null)
OR ([MyField] = [Forms]![Update Mailer Form]![Dept Selector]))

The expression returns True when the text box is null. Since the condition
is True for all records, they are all returned. When text text box is not
null, the expression is true only for those fields that match.

 

[Mac]

Many thanks Allen, it worked a treat.

Regards

Andy