Handle null values in formula

[vijip2001]

All,

I am trying to write a formula in crystal report 8.0.1.
I want to handle a number column that is null.
Can someone please help?

Here is the formula:
if isnull({rstQtPrd_ttx.PCS_CHARGED}) = true or
trim({rstQtPrd_ttx.PCS_CHARGED}) = ""
then {rstQtPrd_ttx.PCS_CHARGED}=0
else
{rstQtPrd_ttx.PCS_CHARGED} = {rstQtPrd_ttx.PCS_CHARGED}

This formula returns FALSE.

Thanks,
VIji

 

[fisheromacse]

the formula seems to be working. it is telling you that {rstQtPrd_ttx.PCS_CHARGED} = 0 is not true (that {rstQtPrd_ttx.PCS_CHARGED} is not equal to zero), or that {rstQtPrd_ttx.PCS_CHARGED} = {rstQtPrd_ttx.PCS_CHARGED} is not true (that {rstQtPrd_ttx.PCS_CHARGED} is not equal to {rstQtPrd_ttx.PCS_CHARGED})

maybe modify your formula to the below:
if (isnull({rstQtPrd_ttx.PCS_CHARGED}) or
trim({rstQtPrd_ttx.PCS_CHARGED}) = "")
then {rstQtPrd_ttx.PCS_CHARGED} := 0
else
{rstQtPrd_ttx.PCS_CHARGED} := {rstQtPrd_ttx.PCS_CHARGED}

 

[vijip2001]

I tried:

if (isnull({rstQtPrd_ttx.PCS_CHARGED}) or
trim(cstr({rstQtPrd_ttx.PCS_CHARGED})) = "")
then {rstQtPrd_ttx.PCS_CHARGED} = 0
else
{rstQtPrd_ttx.PCS_CHARGED} = {rstQtPrd_ttx.PCS_CHARGED}

This returns a TRUE.
How do i make it return 0 if the value is null?

Thanks for the help.

Viji

 

[lbass]

if (
isnull({rstQtPrd_ttx.PCS_CHARGED}) or
trim(cstr({rstQtPrd_ttx.PCS_CHARGED})) = ""
) then
0 else
{rstQtPrd_ttx.PCS_CHARGED}

-LB

 

[vijip2001]

Tried it. It sill returns only TRUE. Does not return 0.

 

[lbass]

Please show the formula you actually created and also explain where you created this. The formula I showed would not return true.

-LB

 

[vijip2001]

I created a formula column in the Seagate crystal reports version 8.0.1.0.
The column in the recordset rstQtPrd_ttx.PCS_CHARGED is a number column.
The formula i used is :

if (
isnull({rstQtPrd_ttx.PCS_CHARGED}) or
trim(cstr({rstQtPrd_ttx.PCS_CHARGED})) = ""
) then
0 else
{rstQtPrd_ttx.PCS_CHARGED}

When i execute the crystal report, i get the value diplayed as TRUE and i want it to return 0.

The record set rstQtPrd is populated using the query:

SELECT sum(PROD_PIECES) PCS
FROM TEST_TABLE WHERE Turn = RUNNING_Turn_NO And Turn_Date = RUNNING_Turn_Date

The query runs against a RDB database.
The values for RUNNING_Turn_NO and RUNNING_Turn_Date
are passed from another query. Since not all the rows have values in PROD_PIECES value, it is showing as null for blank rows and i need to display it as 0.

If i can check the null value in the query itself, that would be fine too. But none of the NVL, IFNULL, ISNULL works in the query part.

Thanks for all the help.
Viji

 

[lbass]

I'm sorry, but the formula will not return "true"--so where are you creating this formula? You should be creating it in the field explorer->formula->new and then dragging it onto the report canvas.

-LB

 

[vijip2001]

It works.I am not what extra characters i had. I deleted the field and recreated it and it works.
Thanks for all the help.

Viji