Troubleshoot IIf statement 1

[Celeste025]

Im having an issue with an IIf statement:

=IIf(IsNull([Parent Group]),"No Parent Group",[Parent Group])

It should print "No Parent Group" if the field is blank, otherwise print the name of the Parent Group. I use this same type of statement throughout my reports with no problem.

However, this one is printing #error and I'm not sure why.

Thanks

 

[930driver]

"It should print "No Parent Group" if the field is blank"

Not necessarily. Blank might be NULL, but it might also be an empty string. An empty string is not a Null. If it's the latter IsNull() will evaluate to False.

One way around this is to concatenate an empty string to the variable, turning a Null into an empty string, and test that.

IIf(Len([Parent Group] & &quot;&quot < 1, etc...
&quot;The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!&quot;

 

[CosmoKramer]

Celeste,

Try this:

=IIf([Parent Group] Is Null,"No Parent Group",[Parent Group])

 

[scriverb]

Or try this:
IIF( NZ([Parent Group],&quot;&quot < 0, ....

There certainly is more than 10 ways to skin a cat. . . Bob Scriver

 

[Celeste025]

An empty string means that there might be spaces in the field, but no data, correct?

If so, it's not an empty string, I already checked that. There are no spaces and no data in the fields.

Cosmo, I tried your suggestion and it's still coming up #error.

 

[CosmoKramer]

Celeste025,

It looks like you might be trying this against a bound control. Try adding another text box and set its control source to:

=IIf([Parent Group] Is Null,"No Parent Group",[Parent Group])

You can't use functions like IIf on a bound control.....

 

[930driver]

Celeste,

An empty string does not have spaces, nor anything else. It is however, even though empty, a string, and will never evaluate to Null.

You can check for an empty string using the Len() function. If Len() returns zero you have an empty string. Len() will error if you test a Null value, so to avoid that when you test with Len for empty string concatenate an empty string and test that. Then both Null values as well as empty strings will return zero.

int = Len(myVar & &quot;&quot

In this way if myVar is empty string or Null, Len() will return zero, anything greater is not an empty string. &quot;The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!&quot;

 

[Celeste025]

Thanks once again Cosmo. I created a new unbound text box, put your expression in the control source, and Voila, it works perfectly!