how to show null count as zero

See sql below.  I have loaded this into the Command and am displaying in Crystal Reports detail section.
When count is nothing, the field is blank.  How can I make it show zero so that the report looks better.?

Thanks!



SELECT Month("CL_MAST"."Delete_Date"), Count("PR_MAST"."Emp_No") as ChicoEmpCount
 FROM
"CL_MAST" "CL_MAST" INNER JOIN "PR_MAST" "PR_MAST" ON "CL_MAST"."Loc_No"="PR_MAST"."Loc_No"
WHERE
"CL_MAST"."Loc_No" >= 1000
AND
"CL_MAST"."Loc_No" < 2000
AND
"CL_MAST"."Loc_No" <>  1009
AND
"CL_MAST"."Delete_Date">={d '2005-01-01'}
AND "CL_MAST"."Delete_Date"<={d '2005-12-31'}
AND
"PR_MAST"."Term_Date"  =  "CL_MAST"."Delete_Date"

GROUP BY
Month("CL_MAST"."Delete_Date")
KeithMcElroyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gopatincCommented:
There are a number of things that could be done here

1. Modify the query to return 0 if no count
    Looks like you have a SQL database, in that replace Count("PR_MAST"."Emp_No")  with ISNULL(Count("PR_MAST"."Emp_No"),0)

2. At the front end,in Crystal, create a formula which checks for null and replaces it with 0
    Create a forumla called ChicoEmpCount
    In the formula editor enter
      if isnull({field with ChicoEmpCount}) then 0 else {field with ChicoEmpCount}

   
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MSuchoffCommented:
This is off the top of my head... Try going to Report Options and look for the database section. Then select the option to Convert Null Field Values to Default. I think I've done this in the past to display zeros

HTH
Mike
0
mlmccCommented:
Both good ideas.  Problem with the second idea, is all NULL fields will now show the default value which may not be what you want for some fields.

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.