If data field is blank replace with NULL 1

[junction123]

Hi

I am using CE 9 with Sybase as the datasource. I have a data field that is a string(1) and returns Y. If the field is blank it returns a NULL value.

How can I show this in the crystal report? Currently I am getting blanks for all rows that do not have a valid value when in theory I need to get a NULL.

Thanks in advance!
junction123

 

[synapsevampire]

Your post contradicts itself:

"...If the field is blank it returns a NULL value."

"Currently I am getting blanks for all rows that do not have a valid value when in theory I need to get a NULL."

You state that it returns a null, but that you need a null.

Perhaps you meant that "...If the field is blank it *contains* a NULL value."?

Check your File->Report Options->Convert Null Values to Default. If this is on, then the report will alter Nulls to their default values (strings to blank, numbers to 0).

I'm not sure why you care whether it's null or blank unless you're trying to differentiate between nulls and blanks.

-k

 

[junction123]

Hi K

The users are very particular about the data returned. I'm sorry I was not explicit in my earlier mail. The report should NOT return a blank space if there is no value. For easy readablity, the blank value should show a "NULL" in the row for the particular column.

For eg:

Account SpecialHoldings

0345678

0234567 Y

3456789 Y

4567789


should be:

Account SpecialHoldings

0345678 NULL

0234567 Y

3456789 Y

4567789 NULL

Thanks
junction123

 

[lbass]

Try:

if isnull({table.field}) or
trim({table.field}) = "" then "Null"
else {table.field}

-LB

 

[andie18]

LB -

where would you use this formula? I am having the same problem with one of my reports. Only difference is that some of the 0's do show up but not on all users. I have checked the convert to NULL and made sure that my default value is 0 but still no go. I just saw your post and was thinking of trying it on my report.

So instead of "NULL" I need to see "0

 

[lbass]

andie,

If it is a number field and you have checked "convert null field to default value" then I think you should always see a "0" for a null. You might want to increase the decimals in the number field and see if the 0's that are showing are actually <> 0 by some fractional amount so that they are appearing, while those that are 0 (either actual 0's or former nulls) are not appearing because you might have checked "suppress if zero" in the format field->number->customize screen.

The formula above is for when you do NOT have convert null to default value checked. In that case, for a number you would use:

If isnull({table.number}) then 0 else {table.number}

You would use a formula like this on your report instead of the field itself.

-LB

 

[andie18]

The fields that I want the 0's to display are summaries of formula fields. There is nothing complex about the formulas - very simple stuff like:

if {conthist.rectype} startswith "C" then 1 else 0

My problem with the 0's is appearing on the subreports (this summary report pulls from many places). If I run the subreport alone, I get the same result. Joe Schmoe will display 0 but Tom Hanks will not display the 0. If they have any data to show, the report works just fine.

So if I were to use your formula, what would be the best way to incoporate it into my summary?

thanks by the way for helping me

 

[lbass]

Replace the detail level formulas with:

if isnull({conthist.rectype}) or
not({conthist.rectype} startswith "C") then 0 else 1

Then insert a summary on this. Then you should get summaries that are not null.

-LB

 

[synapsevampire]

LB's formula should work for you, unless you're using a Left Outer or a subreport and there aren't any rows and you're trying to display a value.

Try posting specific information about your report if LB didn't nail it.

Also note that the version of Crystal and the database being used are important design characteristics.

-k