Adding Null Values in Report Footer 1

[LDP]

We have a report where we, among other things, add the total number of particular values of field over a monthly time period. We have documents that are given a status value (SDComment); "approved", "rejected", "Resubmit" etc. and we then total each category(status)for that month in the report footer

I can total the number of each, however, when I try to total the null values in this field in a monthly report footer using the following...

=Sum(IIf([SDComment]="",1,0))

it returns 0.There are records listed during that month with no status set which is correct. Basically,they are ones outstanding and need to be processed.
Any suggestions on summing the records containing this blank field? I've also tried...

=Sum(IIf(IsNull([SDComment],1,0)))

to no avail..

Thanks for your help.

 

[scriverb]

This may work:

=Sum(IIf(IsNull([SDComment]) or [SDComment] = "",1,0))

Let me know how this works.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[LDP]

Thanks Bob, it worked great.

I'm wondering..why do I need an or statement? Are "IsNull" and "" not considered to be the same with ,1,0))setting the count?

Thanks again, Bob

 

[scriverb]

Yes, you are correct. Null is NOT equal to and empty string "", so you must check for them both.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[LDP]

Thanks again Bob.

It was abit confusung because the field is a text field
so I first tried "" to check for a empty string but that didn't work on it's own.

another simple but valuable Tip

Thanks Bob for making a decent product better.

In the big picture, shouldn't Microsoft owe you guys bigtime?

 

[scriverb]

Hi LDP. Thanks for the Star. Thanks for the kind words and remember there is anything that can't be done with ACCESS. You just have to fool it sometimes into thinking it is more powerful than it already thinks it is.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]