Counting Null Fields 2

[suemon]

I am working on a report that reports on the number of two different types of records added to a database weekly. One is a Company name field and the other is a concatenated 'client name' field.

The data looks like this:

Captain Highliner etc.
Captain Highliner Dave Carter etc.
Fretful Architects Sue Carter etc.
Basically Buggered etc.


The first and fourth lines are simply company records and the second and third lines are the contact records.

I need to total the number of contact and company records seperately. So in the above example the results should be Two new contact records and two new company records.

Using Distinct count on the company name field doesn't work because a contact record also has the comany name field listed.

Counting the number of contacts is easy enough but how can I do a distinct count on the number of client name records equal to null?

Any and all suggestions would be greatly appreciated. It is quite possible that I'm going about this completely wrong.

 

[lbass]

Have you tried the following?

if isnull({@contactname}) then 1 else 0

Then right click on this formula and insert a summary (SUM, not count).

-LB

 

[dgillz]

Write a formula:

if IsNull({YourField}) then 1 else 0

Then perform a simple summary on this formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[suemon]

Had also tried that but it didn't work. Could it have something to do with the fact that the 'client name' field is actually a formula field that I've got constructed like this?.....

(if isnull({Salutation})then "" else {Salutation}+ " ")+
(if isnull({FirstName})then "" else {FirstName}+ " ")+
(if isnull({LastName})then "" else {LastName})

The reason behind this formula is that one or more of the fields may be blank and simple concatenation would not pick them up.

Any thoughts?

 

[Naith]

Adjust the prior suggested formulas to:

if IsNull({YourField}) or Length(Trim({YourField})) = 0 then 1 else 0

Naith

 

[Madawc]

Try a pair of running totals. Use a formula field, maybe testing a boolian like

isnull({LastName}) or {LastName} = " "

If the boolian was called @Company, then the company count would test @Company and the contact count would test not @Company

As a first step, create @Company and put it beside the data. Jiggle with it till it says True or False at the right places.

Madawc Williams (East Anglia, UK)

 

[lbass]

Yes. Then change the count formula to:

if {@contactname} = "" then 1 else 0

-LB

 

[suemon]

That works a treat! Now onto the subsequent issue of not being able to count just those records with a contact name.... keeping in mind that the contact name is a formula field. It's gotta be an if isnull statement but for the life of me can't figure out.

Sure to be sleepless.

 

[lbass]

Try:

if {@contactname} <> "" then 1 else 0

Then sum this formula.

-LB

 

[suemon]

Thanks to All of you ... Saved me many frustrating hours!!