IFF statement doesn't produce expected 1

[leishad]

I would appreciate anyone telling me what might cause the following problem in a formula field:

IIF (IsNull({OEORDHDR_SQL.cmt_1}),"Thank-you","sssss&quot this works

IIF (IsNull({OEORDHDR_SQL.cmt_1}),"Thank-you",{OEORDHDR_SQL.cmt_1})this only works for false, if true it is blank

Any suggestions to narrow down the cause would be appreciated.

 

[synapsevampire]

Looks like a buglet, null processing in CR is very odd anyway...

Try:

IF (IsNull({OEORDHDR_SQL.cmt_1})
or
trim({OEORDHDR_SQL.cmt_1}) = "" then
"Thank-you"
else
{OEORDHDR_SQL.cmt_1})

-k

 

[leishad]

Thank-you for the response. When I try this I get the message that I am missing a closing parenthesis after the ""

I had this happen before and couldn't figure out what makes it think I need one there.

Thoughts?

 

[bdreed35]

Try this:

IF (IsNull({OEORDHDR_SQL.cmt_1})
or trim({OEORDHDR_SQL.cmt_1})) = "" then
"Thank-you"
else
{OEORDHDR_SQL.cmt_1}

~Brian

 

[leishad]

Now it says "a boolean is required here" in front of trim.

 

[synapsevampire]

There was an extra paren:

IF (IsNull({OEORDHDR_SQL.cmt_1})
or trim({OEORDHDR_SQL.cmt_1}) = "" then
"Thank-you"
else
{OEORDHDR_SQL.cmt_1}

 

[bdreed35]

My bad. Moved the on closing paren to the wrong place.
Try this now:

IF (IsNull({OEORDHDR_SQL.cmt_1}) or trim({OEORDHDR_SQL.cmt_1}) = "") then
    "Thank-you"
else
    {OEORDHDR_SQL.cmt_1}

~Brian

 

[leishad]

That's it -- thank-you very very much. If you feel up to it I would love to know why Crystal didn't like my original version. It looked as if it interpreted the IsNull correctly until I told it to display a fieldname instead of a string.

Thanks again

 

[tektipdjango]

like in other VB language (msAccess vba...) the iif function evaluates the two parts, true expression and false expression.
When there is a null in the false part, there is a "side effect"
It's the same for instance in the following code

numbervar var:=0;
iif(var=0,0,1/var);
this may cause a "division by zero" error.

The best way to avoid this is to use the if...then...else syntax