Unable to count empty fields

[mcelligott]

I am creating a stats report in the report footer. I am looking to count the number of times the platoon field is not filled in along with if the complaint was founded or unfounded. Here is what I have in the control source for the text field.

=Count(IIf([Founded/Unfounded]="Founded" And IsNull([Platoon]),1))

I have also tried using [Platoon]="" but still keep coming up with 0. I have gone through my test data and there should be 1 record where [Founded/Unfounded]=Founded and [Platoon]= (nothing). I am sure it is something simple, I am just tired from looking at it and need another set of eyes.

Thanks in advance for any assistance you can help.

Bob

 

[dhookom]

IIf() has three arguments. Your usage has only two. Do you undertand that Count() will count true and false values as the same?

Duane
Hook'D on Access
MS Access MVP

 

[mcelligott]

No I was not aware of that.

I have also tried changing the control source for the field to look like:

=Sum(Abs([Platoon] Is Null And [Founded/Unfounded]="Founded"))

and still come up with 0.

When I tried running the following query to make sure, it came up with the 1 record it should (note: [Complaint #] Like "i09*" I put in because the report itself is only looking for those records).

SELECT [Informal Complaints].[Complaint #], [Informal Complaints].Platoon, [Informal Complaints].[Founded/Unfounded], [Informal Complaints].Status
FROM [Informal Complaints]
WHERE ((([Informal Complaints].[Complaint #]) Like "i09*") AND (([Informal Complaints].Platoon) Is Null) AND (([Informal Complaints].[Founded/Unfounded])="founded"));

Any thoughts?