IIF syntax problem...

[Guest]

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

 

[strive4peace]

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
*