Totals - Count

[RobW]

I am having trouble with the count feature. I am writing a cricket database and I need to count the number of times the words "Not Out" appear in a field (called HowOut). I have tried to put "Not Out" in to the criteria box and then created a calculated field to count that. This worked if the player had a "Not Out" entry in the HowOut field. If the player did not, a zero was not put in to the calculated field. Can any one help me to do this or suggest another way of solving the problem?

 

[dcx693]

You can create a totals query, group by the HowOut field, and use "Not Out" as the criteria. Should return to you a single value with a count of how many times "Not Out" appears in the field.

 

[RobW]

I got that far, but i need a total for each different player. If the individual player has a "Not Out" entry in the HowOut field, it works fine, but if they don't the calculated field does not put zero. Do you know how to sort this?

 

[dcx693]

Have you tried grouping by player, then on the HowOut field?

 

[neileg]

Access can't count something that isn't there! If you join another query that contains a full list of all your players to the query that returns your count, you can use Nz() to convert a null entry to a zero.

 

[RobW]

Thanks for all your help, got round the problem by creating an IIF statement:
IIF([QtyNotOut] >0, [QtyNotOut],0)

 

[neileg]

You could use Nz([QtyNotOut],0)