Counting non-Null/Blank records in text field 1

[Genepoz]

In Crystal Reports 8, in the Report Footer, I need to get Count to stop counting text fields that have nothing in them. I'm surprised that it's counting them at all. I can't use DinstinctCount in this case.

 

[Genepoz]

I did find a cheesy way to do this. I created a formula field:

if {TABLE.TextField}="" or IsNull({TABLE.TextField}) then 0 else 1

Then I suppressed it and put it on top of the text field. Then used Sum to get a count of non-blank fields. Not very satisfying, but is this the way it's SUPPOSED to work?

 

[ianoctdec]

Hi,

Create a formula with nothing in it: @InvalidField.
Then create a formula:
@CountedField
if not isnull(table.field) then
table.field
else
@InvalidField

Insert your distinct count on @CountedField instead of table.field. It won't count the nulls.

The solution is from this forum.

Dana

 

[Genepoz]

Hi IanOctDec. Thanks for responding, but that doesn't work, because as I said in my original post, I can't use DistinctCount (there are duplicate titles). If I use Count on @CountedField it counts all records, null or not.

 

[ianoctdec]

Hi,

I misread the type of count you need. Your solution is the way to go then.

Dana

 

[lbass]

You could have gone to file->report options and unchecked "convert nulls to default values." Then you should have been able to insert a distinctcount on the text field--as long as the field did not contain a space.

Note that your formula should have checked for the null first (if you want to stay with that method):

if IsNull({TABLE.TextField}) or
{TABLE.TextField} = "" then
0 else 1

Because your formula seemed to work, it indicated to me that the text field was never null--and therefore it seemed likely that the conversion option was set.

-LB

 

[Genepoz]

Thanks, LB, but it was unchecked. And the order in which I put IsNull and "" didn't make any difference. Are there times when it will?

I am having another weird problem though: The Sum method is producing correct results for each field except the 1st one on the left (just to the right of Group Name). It's giving # larger than the # of records in the report. I can't figure it out.

 

[lbass]

Well, that just means that your field is never null, although it may be blank. You must ALWAYS check for nulls first--otherwise the nulls will not be picked up at all.

I can't tell what the first one on the left is, but try this formula instead:

if isnull({table.text}) or
trim({table.text}) = "" then
0 else 1

You might have a different number of spaces in the field, and trim() will remove them.

-LB