[AccessD] Count Problem

Ok, what about - IIf((Count([Status]))=0,Null,Count([Status]))
This will group the Count([Status]) as a value to be able to compare 
it to 0.  Then, if that result is 0, Null, if not, show the value.


At 03:44 PM 12/8/08, you wrote:
>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
>
>
>--
>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