Hi Adam,
how about this:
=iif(
Sum(
IIf(
[Product Type]="Allied"
,1
,0
)
)=0
,0
,Sum(
IIf(
[Product Type]=Forms![Allied or Monster]!ProductTypeV
And
[Packet Sent to Wholesaler YN]=True
,1
,0
)
)
/
Sum(
IIf(
[Product Type]="Allied"
,1
,0
)
)
)
then, use the Format property of the control to make zeros display as "-"
on format code -- there are 4 parts
1. format for positive numbers
2. format for negative numbers
3. format for 0 (zero)
4. format for null
ie:
#,##0;-#,##0;"-";"-"
Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
AdamZ wrote:
[Quoted Text] > I have a crazy control source already which divides one expression in to
> another (see below). The problem is the second half may be "0" and is throws
> #num! when it is. I'd like to show a "-" instead of #num!". Any ideas on
> how to format the IIF (i've tried a few differnt ways to no avail.)
>
> Full code as is:
>
> =(Count(IIf([Product Type]=Forms![Allied or Monster]!ProductTypeV And
> [Packet Sent to Wholesaler YN]=True,[Wholesaler
> ID],Null)))/(Count(IIf([Product Type]="Allied",[Wholesaler ID],Null)))
>
> Thanks,
> Adam
|