Return zero for a null value

[echorley]

I have a query that looks for the number of incompletions that a quarterback throws. If there are no incompletions, then I want to return a value of zero.

In the Field "Pass Play Status" I have set the criteria of "Incompletion"

If there are incompletions, then the following expression returns the proper number of incompletions:

Count of Incompletions: IIf(IsNull([Pass Play Status]),0,Count([Pass Play Status]))

However, if there are no incompletions, I get no values at all (Null).

I also tried:

Count of Incompletions: NZ(Count([Pass Play Status]),0)

but to no avail! Any ideas? Thanks!

 

[AncientOne]

Have you tried:


IIf(IsNull(Count([Pass Play Status])),0,Count([Pass Play Status]))?

 

[echorley]

Yes and to no avail

I have tried a variety of different things and got some of them to work ... sort of.

Below the column "Pass Play Status" I put "Incomplete" and then Is Null in the "or" section. The problem is when there is a complete pass, the query returns two rows - one for the count of Incomplete and another with the count of null values.

This query is part of a larger query where I summarize the passing statistics. So two rows would screw that up. Thanks for your help and if you have any other ideas please let me know.

 

[echorley]

That works except for...

My quarterbacks are not showing up. Here is the SQL:

SELECT Count(*) AS CountOfIncomplete
FROM [Offense Game Input Table]
WHERE ((([Offense Game Input Table].[Pass Play Status])="Incomplete"));

I replaced PlayerID with [Offense Game Input Table].Quarterback
(the column Quarterback is just the number of the quarterback)

SELECT [Offense Game Input Table].Quarterback, Count(*) AS CountOfIncomplete
FROM [Offense Game Input Table]
WHERE ((([Offense Game Input Table].[Pass Play Status])="Incomplete"));

This gives me null values again.

If there are two quarterbacks then I need to sum to be grouped by both.

Thanks again for the help and if there are any more ideas to get me over the hump that would be great!!

 

[echorley]

GOT IT!!!!!!!!!!!!1

Took quite a bit of looking, but (again thanks to Pat H.)

Sum(IIf([Pass Play Status]="Complete",1,0))

gives the number of completions for each quarterback individually on my report!!!

Thanks to all!!!