Distinct Count is counting Null as 1 CR XI 1

[debbiepy]

I have a union query in MS Access 07 that unions 3 tables into one. The union query has "" as OpportunityID for the two tables that do not contain an opp ID. The issue is when I perform a distinct count it sums all the blank into 1 distinct count in Crystal REports XI.

How do I get the distinct count summary to ignore the nulls. It is a summary object so I cannot get to the code, or can I, to add Not isnull().

 

[lbass]

In Crystal, create a formula {@null} by opening a new formula and saving and closing it without adding anything to the formula area. Then create a second formula {@OppID} like this:

if {table.oppID} = "" then
{@null} else
{table.oppID}

Then insert your distinctcount on this formula.

-LB

 

[debbiepy]

it worked perfectly! Thank you