Crystal Reports formula: IsNull + Iif

Question

There are hints of the answer to this question here and there on this site, but I'm asking a slightly different question.

Where does Crystal Reports document that this syntax does not work?

Trim({PatientProfile.First}) + " "
    + Trim(Iif(
        IsNull({PatientProfile.Middle}) 
        , Trim({PatientProfile.Middle}) + " "
        , " "
        )
    )  
+ Trim({PatientProfile.Last})

I know the solution is

If IsNull({PatientProfile.Middle}) Then
    Trim({PatientProfile.First})
        + " " + Trim({PatientProfile.Last})
Else
    Trim({PatientProfile.First})
       + " " + Trim({PatientProfile.Middle})
       + " " + Trim({PatientProfile.Last})

but how are we supposed to figure out we can't use the first version?

The documentation for IsNull says

• Evaluates the field specified in the current record and returns TRUE if the field contains a null value

and Iif gives

• [Returns] truePart if expression is True and falsePart if expression is False. The type of the returned value is the same as the type of truePart and falsePart.

I suppose if you stare at that line about "type of the return value" you can get it, but...

Bevan

I don't have CRXI handy today, but what doesn't work about the first one? Does it just get the spacing wrong (which is a logic error in the code - you have an extra Trim() in there) or does it fail in some other way? – Jacquelynnjacquenetta

For records where .Middle is NULL, CR can't evaluate the string value in Trim(.Middle), and decides the value of the Iif is NULL, even though I have that explicit test in there. – Bevan

Answer 1

I think CR evaluates both IIFs true and false parts. Because you have "Trim({PatientProfile.Middle})" part there, which will be evaluated aganst null value, CR formula evaluator seems just fail.

Shin

Yes, but I'm trying to figure out where in the bloody documentation it says this, instead of making everyone who runs into it Google for the answer. After all, one or two of us know how code is supposed to work... – Bevan

From CR help: Every argument of the IIF function is evaluated before the result is returned. Thus, you should watch out for undesirable side effects when using IIF. For example, if falsePart results in division by zero, an error will occur, even if expression is True and so truePart is returned. – Shin

Answer 2

Where does Crystal Reports document that this syntax does not work?

I doubt there is anyplace large enough in the entire universe to document everything that does not work in Crystal Reports...

Spontaneity

Answer 3

I know I'm years late on this one, but I came upon this question while trying to figure out the same thing. Funny enough, I couldn't even find the answer in Crystal Reports documentation, but instead in a link to IBM.

Baiscally, if you're using Crystal Reports 8.x or 10.x, ISNULL and IIF don't work together. From the site:

Cause

There is a defect in Crystal Reports 8.x and 10.x that prevents the above formula from working correctly. The 'IIF' and 'IsNull' commands cannot function together, and that includes attempting to use "Not" to modify the IsNull command; for example, IIF(Not IsNull ()).

Resolving the problem

The workaround is to use an "If-Then-Else" statement. For example,

If IsNull({~CRPT_TMP0001_ttx.install_date}) Then "TBD" Else "In Progress"

So if you're using CR 8.x or 10.x (which we are), you're out of luck. It makes it REAL fun when you are concatenating multiple fields together and one of them might be NULL.

Sandhurst

Note that even if you are concatenating multiple fields together, the syntax is not too bad, since Crystal supports things like this: (if IsNull({Col1}) then 0 else {Col1}) + (if IsNull({Col12}) then 0 else {Col2}) – Riordan

Answer 4

try this:

currencyvar tt;
currencyvar dect;
tt :={ship.comm_amount};
dect := tt - Truncate(tt);
tt := truncate(tt);
dect := dect * 100;
if dect = 0 then
UPPERCASE('$ ' + ToWords (tt,0 )) + ' ONLY'
else
UPPERCASE('$ ' + ToWords (tt,0) + ' And ' + ToWords(dect,0)) + ' ONLY ';

Accalia

What question is this supposed to be answering? – Bevan