if then else not working as desired 1

[chrisgeek]

I'm using an IF THEN ELSE statement to display a string. the field type is varchar and can be null. My Statements are:

IF ISNULL ({storedprocedure.fieldname})Then "None" ELSE
IF TRIM({storedprocedure.fieldname})= "" Then "NONE" Else
IF {storedprocedure.fieldname} = "Contact" THEN "Contact" Else
IF {storedprocedure.fieldname}= "A" Then "First" Else
IF {storedprocedure.fieldname}= "B" Then "Middle Row" ELSE
IF {storedprocedure.fieldname}= "C" Then "LastRow" ELSE
IF {storedprocedure.fieldname}= "D" Then "Door" ELSE
IF {storedprocedure.fieldname}= "M" Then "Mezz";

I have covered all the potential values in this field.

This is working when the field is not blank or NULL but not displaying any value if it is. Any ideas on what I'm doing wrong?

 

[lbass]

Are you linking the stored procedure to another table? If so use a left join FROM that table to the sp, and use no selection criteria on the sp fields.

-LB

 

[chrisgeek]

No it's just a SP and this is the primary table in the SP. I'm assuming that there is something wrong with the ISNULL or the "" statement. The history of this is I have an primary application/database (which I can't access) that displays "contact', "First" etc... It's interfaced to the DB/application that I am reporting off of. However when it's interfaced the field is truncated. I need to display it on the report in the same fashion as it's entered by the end user.

 

[chrisgeek]

I created a formula as follows:
IF ISNULL ({storedprocedure.fieldname}) THEN "None:" ELSE
SELECT {storedprocedure.fieldname}
Case "Contact":
"Contact"
Case "M":
"Middle"
Case "A":
"First"
CASE "C":
"Last Row"
Default:
"None"

This seems to be working for me. Thanks for your help.

 

[lbass]

There is nothing wrong with your formula (if this is the entire formula). Could the field already be set to default to some other value in the SP if null?

-LB

 

[chelseatech]

Check your report options. Make sure you have NOT set "Convert Database NULL values to Default".

That will change the NULL values to ""

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm