Not Null Count

[dancoe2004]

Hi

I'm having some issues with a count function within my report. My report is a set of flights grouped by commander. Within the commander footer I want to show the percentage of their flights for which there is an attached comment. At the minute however I am unable to even count the number of Not Null entries in the comment field. The expression I'm using is

=Count(Not IsNull(Trim$([Comments])))

however this is simply giving the total count including all the blank entries, any ideas?

 

[vbaInet]

* Have you tested the field to see if it contains zero-length string?
* What is the value of the Allow Zero Length String property of the field in the table?

The correct syntax is:

=Count(IIf(IsNull([Comments]), 1, Null))

... but this would depend on whether it has zls or not.

 

[dancoe2004]

Just tried your solution and it resulted in all counts coming in as 0 which I guess suggests there are no zero length fields.

I have checked the zero field length allowed property in the table and this is set to yes so shouldn't be an issue.

Have also tried adding a trim function so the expression becomes

=Count(IIf(IsNull(Trim([Comments])),1,Null))

but this seems to have no impact at all. How can I get it to distinguish between blanks and non blanks?

 

[vbaInet]

Just tried your solution and it resulted in all counts coming in as 0 which I guess suggests there are no zero length fields.

It suggests that there are no Null values in the Comments field.

I have checked the zero field length allowed property in the table and this is set to yes so shouldn't be an issue.

This is where your problem begins and I suspect that somewhere in your database you are initialising or setting the Comments value to zls (i.e. "").

Have also tried adding a trim function so the expression becomes

=Count(IIf(IsNull(Trim([Comments])),1,Null))

You won't Trim() if you're going to use IsNull(). Or are you having doubts that your Comments field might contain just spaces? The following should work:

=Count(IIf(Len([Comments] & "") = 0, 1, Null))

By the way, in what section of your report are you putting this code?

 

[boblarson]

I would use

=Sum(IIf(Len([Comments] & "") = 0, 1, 0)

Instead of using NULL as the opposite.

 

[vbaInet]

They both achieve the same goal but why count something when it can be ignored? Null is ignored in aggregate functions unless you use "*". You would imagine that there will be performance benefits when Null is ignored as opposed doing a sum on each record.

 

[boblarson]

They both achieve the same goal but why count something when it can be ignored? Null is ignored in aggregate functions unless you use "*". You would imagine that there will be performance benefits when Null is ignored as opposed doing a sum on each record.

Negilible in these days of the current computers. And, I've noticed that COUNT isn't always reliable where Sum in the method I gave has never let me down.

 

[vbaInet]

True. I'm sure you've dealt with some horrendous legacy systems still in existence.

 

[dancoe2004]

Thanks for your help, I have now managed to solve the issue.

I did try to use

=Sum(IIf(Len([Comments] & "") = 0, 1, 0)

however it kept coming back with an error that I'm sure was pretty simple but I couln't solve

=Count(IIf(Len([Comments] & "") = 0, 1, Null))
This on other hand worked first time!

 

[vbaInet]

It should have worked, it was just missing a closing parentheses at the end.

Glad we could help.