[AccessD] Count Problem

What I am trying to do is do a count on the field status for each value it can contain for all the records in a table. If a count of the field returns zero I want to see NULL. If it returns any other value I want to see the actual value. Hope that makes sense. Here is whole SQL string

SELECT IIf([Stat_Code]="RP","RL",IIf([Stat_Code]="SP","SL",
[Stat_Code])) AS Status, [tbl Status Codes Active].Stat_Desc, DateSerial(Year(Date()),Month(Date()),1)-1 AS StatusDate1, IIf(Count([Status])=0,Null,Count([Status])) AS StatusCount, 
[tbl Status Codes Active].Area, [tbl Status Codes Active].UI, 
[tbl Status Codes Active].SortOrderSummary
FROM [qry Well Status 1 Month Prior with Area] 
RIGHT JOIN [tbl Status Codes Active] ON ([qry Well Status 1 Month Prior with Area].Status = [tbl Status Codes Active].Stat_Code) 
AND ([qry Well Status 1 Month Prior with Area].Area = [tbl Status Codes Active].Area)
GROUP BY IIf([Stat_Code]="RP","RL",IIf([Stat_Code]="SP","SL",[Stat_Code])), [tbl Status Codes Active].Stat_Desc, DateSerial(Year(Date()),Month(Date()),1)-1, [tbl Status Codes Active].Area, [tbl Status Codes Active].UI, [tbl Status Codes Active].SortOrderSummary;

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Jones
Sent: Monday, December 08, 2008 3:40 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Count Problem

I'm not sure I understand.  Are you trying to get either 0 or Null 
for the result?  If so, I think you're missing a closing parenthesis 
after [Status]=0.  What I read right now is going to count Null 
and/or Count(Count([Status])).
Susan

At 02:09 PM 12/8/08, you wrote:
>I am having trouble with the count function.
>If I do a count of the field Status in a table for a status code 
>of  "FL"  I get 0
>If I use the following formula I get 1. Is it counting the number of nulls?
>StatusCount:Iif(Count([Status]=0,Null,Count([Status]))
>
>If so how do I get it to return a value of NULL?
>
>Thanks.
>
>
>Chester Kaup
>
>Engineering Technician
>
>Kinder Morgan CO2 Company, LLP
>
>Office (432) 688-3797
>
>FAX (432) 688-3799
>
>
>
>
>
>No trees were killed in the sending of this message. However a large 
>number of electrons were terribly inconvenienced.
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>__________ Information from ESET NOD32 Antivirus, version of virus 
>signature database 3671 (20081208) __________
>
>The message was checked by ESET NOD32 Antivirus.
>
>http://www.eset.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com