#Num!

  • Thread starter Thread starter Ann
  • Start date Start date
A

Ann

I am working on a report grouped by month for completed policies. I wrote
the following to create percents for STD and NSTD. This is the one for NSTD.

=Count(IIf([PolicyStandard] Like "NSTD" And
[Duration]<=13,0))/(Count(IIf([PolicyStandard] Like "NSTD",0)))

These work fine as long as every month has at least one completed policy for
STD and one for NSTD, but if there is only STD policies completed and no NSTD
policies completed I receive #Num! for the NSTD. I tried to use Nz but
couldn't figure out where to place it to get a 0% can anyone help me out?
Thanks in advance.

I also need to create these percents as a year-to-date percent on each group.
 
The problem here might be division by zero.

Add a text box to the Form Footer section, and give it these properties:
Control Source =Count([Policy] = "NSTD")
Name txtNstdCount
Format General Number
Visible No

Now test if that returns a zero in your main text box, i.e.:
=IIf([txtNstdCount] = 0, 0, Count(([Policy] = "NSTD")
AND ([Duration] <= 13)) / [txtNstdCount])
 
Ann said:
I am working on a report grouped by month for completed policies. I wrote
the following to create percents for STD and NSTD. This is the one for NSTD.

=Count(IIf([PolicyStandard] Like "NSTD" And
[Duration]<=13,0))/(Count(IIf([PolicyStandard] Like "NSTD",0)))

These work fine as long as every month has at least one completed policy for
STD and one for NSTD, but if there is only STD policies completed and no NSTD
policies completed I receive #Num! for the NSTD. I tried to use Nz but
couldn't figure out where to place it to get a 0% can anyone help me out?
Click to expand...


Count counts all non-Null values regardless of how you
arrive at the values. With that in mind, that error is
probably caused by more factors than just the expression
(e.g. where clause filters out all records so there is
nothing to count or ???).

Note that you should use = to compare exact values. Like is
intended for wildcard matches.

Try something more like this:

=IIf(Sum(IIf(PolicyStandard = "NSTD", 1, 0)) = 0, 0,
Sum(IIf(PolicyStandard = "NSTD" And Duration <= 13, 1, 0)) /
Sum(IIf(PolicyStandard = "NSTD", 1, 0)))
 
Ann said:
I am working on a report grouped by month for completed policies. I wrote
the following to create percents for STD and NSTD. This is the one for
NSTD.

=Count(IIf([PolicyStandard] Like "NSTD" And
[Duration]<=13,0))/(Count(IIf([PolicyStandard] Like "NSTD",0)))

These work fine as long as every month has at least one completed policy
for
STD and one for NSTD, but if there is only STD policies completed and no
NSTD
policies completed I receive #Num! for the NSTD. I tried to use Nz but
couldn't figure out where to place it to get a 0% can anyone help me out?
Thanks in advance.

I also need to create these percents as a year-to-date percent on each
group.
Click to expand...


It took me a minute to figure out the logic of your Count(IIf())
expressions, where the IIf() functions may return Null, which is not
counted. I probably would have done it differently, but this should work --
*except* that if there are no NSTD policies, you'll end up dividing by zero.
If you want to return 0 when there are no NSTD policies, I suggest that you
wrap the whole thing in another IIf expression, like this:

=IIf(
Count(IIf([PolicyStandard] Like "NSTD",0))=0,
0,
Count(IIf([PolicyStandard] Like "NSTD" And [Duration]<=13,0))
/
(Count(IIf([PolicyStandard] Like "NSTD",0)))
)

I wrapped that onto multiple lines for clarity, but in practice it must all
be on one line in your controlsource. I can't swear I've got the
parentheses right, either, though I believe they count up correctly.
 
Back
Top