Skip to Content
0

if field is empty then replace it by another field.

Apr 09, 2017 at 05:48 PM

71

avatar image
Former Member

i want to replace marks by status_name if marks is null. when the marks value is null, instead of showing status_name , my reports simply shows blank. Btw ,status_name is varchar and marks is number datat type. Is this because of data type difference?

if isnull ({DataTable1.marks})  then 
({DataTable1.status_name})
else
 Totext({DataTable1.marks})<br>
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Abhilash Kumar
Apr 10, 2017 at 08:20 AM
0

Hi Hangkum,

Open the formula and look for a drop-down on the top that reads 'Exceptions for Nulls'; change that to 'Default Values for Null'.

Modify the formula to:

if isnull({DataTable1.marks}) OR {DataTable1.marks} = 0 then 
({DataTable1.status_name})
else
 Totext({DataTable1.marks})

-Abhilash

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

thanks for the reply but its not resolved yet .

Under Report>Report Options> I checked Convert Database NULL values to default and i also checked Convert other NULL values to default.

But still my report shows me the value 0. Btw i want only null values to be replaced by Status_name . If marks =0 then i want the report to show 0. so this is the formula i am using.

(i am using a crosstab and data is pulled from SQLSERVER)

if isnull({DataTable1.marks})  then 
({DataTable1.status_name})
else
Totext({DataTable1.marks})
0
Abhilash Kumar
Apr 10, 2017 at 03:47 PM
0

Hi Hangkum,

If you convert NULL values to default, all NULL values in a 'number' column would become zero whereas all NULL values in a 'string' column would become blanks.

Uncheck those two options as well as set the drop-down in the formula editor back to 'Exceptions for Nulls' and the original formula you posted should work fine.

-Abhilash

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

ok i cannot find Exceptions for Nulls drop-down. There is only one drop down in the Formula Workshop . It has two options : Crystal Syntax and Basic Syntax.

(I am using visual studio 2008 and crystal reports is installed there)

0
Former Member

This is the screenshot of the Formula editor:

0