Display String for Number data type

[jsttuchm3]

I have a report where charater data like "NULL" to be displayed for number data types.
Ex: - If and ID is blank in the database then display NULL in the report. ID is of Number data type in Database.
Need to display the same for Date values too.
If the start and End dates are blank then display NULL.
How can this be achieved through formula.
I tried the following

StringVar x: {rpt.planID};
If IsNumeric(x) Then {rpt.planID}
Else "NULL"

Didn't help.

StringVar x := cstr({rpt.PlanID});
If IsNull(x) Then "NULL"
Else x

This also didn't help

Also tried with ToText function couldn't get through.

How to achieve the above functionality using Formule to display Character data "NULL" for Number & Date data types in crystal Reports. I am using Crystal reports XI.

Thanks,
RP

 

[GJParker]

You need to first check if the value is null and the output appropriately

Numbers :

If IsNull({MyTable.Field}) Then
'NULL'
Else
CStr(({MyTable.Field})


Dates

If IsNull({MyTable.Field}) Then
'NULL'
Else
CStr(({MyTable.Field}, 'dd-MM-yyyy')

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[jsttuchm3]

I have one question on this solution. By converting numeric to character using cstr function, the numeric data is represented as 9,999.

I could remove the decimal part from the data and couldn't do away with comma.

Is there a way to remove the comma from data.

Thanks for the help GP.

Thanks,
RP

 

[GJParker]

Here ya go

CStr(({MyTable.Field},0,'')

Gary Parker
MIS Data Analyst
Manchester, England

 

[jsttuchm3]

It works.
Appreciate your help.

I am using the following code

If IsNull({FMX_SHPM_OPMZ_RPT.PLAN_ID}) THEN
'NULL'
ELSE
cstr({FMX_SHPM_OPMZ_RPT.PLAN_ID},0,"")

For only this data field the NULL validation is not working and shows " 0 ". Used the same code for other data fields and it works fine.

If IsNull({FMX_SHPM_OPMZ_RPT.OPTIMZ_REQ_ID}) THEN
'NULL'
ELSE
cstr({FMX_SHPM_OPMZ_RPT.OPTIMZ_REQ_ID},0,"")

This works fine.

I checked the formula field created if there is any default value set to zero. But couldn't find any.

Am i doing something different here in this scenario.

Thanks,
RP

 

[GJParker]

check the value of {FMX_SHPM_OPMZ_RPT.OPTIMZ_REQ_ID} and i'll bet you this is a zero and not null.



Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

...so you could just change the formula to:

If IsNull({FMX_SHPM_OPMZ_RPT.PLAN_ID}) or
{FMX_SHPM_OPMZ_RPT.PLAN_ID} = 0
THEN
'NULL'
ELSE
cstr({FMX_SHPM_OPMZ_RPT.PLAN_ID},0,"")

-LB