Crystal Reports formula field not working when command field is zero

Question

I have created a Crystal Report which has a command field

count(service_ID):

select count(service_ID) 
from inbound_service 
where service_ID={?Service_ID}

and it is returning correct count values according to the database.

In the main report I have a formula field

@Service_ID:

if count(service_ID) =0 
then "ID Does not exists"
else {?Service_ID}

It is working if the command field is not equal to 0, but not working for count(service_ID) = 0.

If I try to show the command field value in a report, it is giving correct values for non-zero counts but showing nothing for 0 counts.

Ryan Ryan · Accepted Answer · 2012-03-26T16:12:53

If there are no rows in the DB that match {?Service_ID} then the result set from your SQL command will be empty and there will be no rows; it will NOT be the case that count(service_ID)=0. Instead you will have to use isnull(count(service_ID))

Edit: I overlooked the fact that you had a count() in the SQL Command itself and that the second "count" refers to that field and not a Crystal summary function. In this case, a single row with the value 0 should be returned.

Crystal Reports formula field not working when command field is zero

1 Answers