Skip to Content
avatar image
Former Member

if field is empty then replace it by another field.

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>
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Apr 10, 2017 at 08:20 AM

    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

    Add comment
    10|10000 characters needed 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})
  • Apr 10, 2017 at 03:47 PM

    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

    Add comment
    10|10000 characters needed characters exceeded