Divide By Zero, IIF Not Helpful 2

[xbigblue]

Hello friends,

I have an Access 2000 report that Must sum counts of "goals" and their corresponding "accomplishments" by control group (county=groupfooter0 in report) and final totals(groupfooter1). The problem is, it must also calculate a percentage of accomplishment (Number of individuals accomplishing the goal divided by the number of individuals with the goal.) The divisor sometimes is zero (no one had the goal) in the group or final totals.

In the group and final total footers, I have unbound text boxes to receive the down totals; example
ControlSource=Sum(SecureEmployment) which works fine for the column totals. For the division field, I first tried:

=Sum(AccompSecureEmployment0/Sum(SecureEmployment)

which works when there is a non-zero value in Sum(SecureEmployment) (the goal) but fails when there is a zero total. Next I tried:

=IIf([Sum(SecureEmployment)]=0,0,[Sum(AccompSecureEmployment)/Sum(SecureEmployment)])

as the obvious solution but at execution Access prompts for
sum(SecureEmployment) and the other sum in the IIF stmt. as parameters. I tried qualifying the field name as Sum(Me.SecureEmployment..... but that did not work, it still prompts me for Sum(Me.SecureEmployment).

I know it is theoretically possible to calculate one's own totals in VBA but I have just finished a 5 day horror trying to match the actual totals to what I get using VBA approach. Advice I've seen in various sources says "DO NO TRY TO DO THIS, IT WON'T WORK!". Typically the results of final totals are 2x, 3x or worse multiples of the county totals, AND sometimes the printed report will not match the Print Preview report. I tried using all the recommeneded methods of preventing these errors, (using PrintCount or FormatCount testing, using the Retreat event; no combination ever worked.

I have to be close; I know IIF works on a variable not in a SUM function. But I have to have the sum for the division.
What am I doing wrong, or not doing?

Thanks, hope someone has solved this before --
xbigblue

 

[JoeMiller]

Rather than using the values on the report in the controls why don't you use DSum and go out to the query feeding the report to get the answer?

=DSum("[MyField]","MyQuery","[MyCriteria]=[MyCriteria]&quot

Then you can use the DSums to get the percentage like so:

=DSum(AccompSecureEmployment0)/DSum(SecureEmployment)

And you can test for Zero in the DSum divisor like this:

=DSum(AccompSecureEmployment0)/iif(DSum(SecureEmployment)=0,1,DSum(SecureEmployment))


HTH Joe Miller
joe.miller@flotech.net

 

[xbigblue]

Joe,
Thanks for your post. I have to ask you for a little more guidance on what you suggest I do. In the DSUM usage, should I develop them in unbound(not visible) textboxes in my COUNTY footer, and then reference those textboxes in actual percentage calculation textboxes that use the IIF? Or do I need to develop them as variables in VBA code? I hate to get into the VBA due to all the problems I referenced in my first post.

Second question: I tried introducing just one DSUM as the control source for an unbound textbox at COUNTY total time to make sure I understood its syntax and what it generated.
There are about 5000 records in the answer set to the "feeding" query, and just one DSUM killed the performance of the report. Seems like the (4) DSUMS I'll have to include will completely hog-tie the report. Is there a solution you can see that does not require going out and running those four long-runners?

You were so quick to respond to the first post, thanks so much. Sorry I have not been able to answer as quickly.
Hope you have more insights into this one.

Many thanks,
xbigblue

 

[JoeMiller]

Well it may be better in this case (due to the amount of records) to make a separate query that performs the calculations you need and then pull the data using a DLookup function. But I'm also inclined to visit why the original idea is not working, any chance you could send me a copy of the db with a minimal dataset so I can have a look see?

Joe Miller
joe.miller@flotech.net

 

[xbigblue]

Joe,

I have tried joining another query to my "feeding" query to develop the subtotals I need for the report and I'll see what I can do with that idea.

I don't know if you meant MY original idea or YOUR suggestion. If it's MY original technique with the IIF's that don't work, the report is presently in a state of confusion, it's a mixture of stuff that works and doesn't work and I would not want to send it to you. And, this is a replicated database that I am in the middle of splitting into a DATA database and a PROGRAMS database to hopefully solve lingering replication problems. I would probably have to spend more time constructing a package to send you than I can afford right now.

But, if I don't solve the zero divisor problem on my own, I will try to get that package together and zip it to you. I appreciate your offer.

Thanks
xbigblue

 

[xbigblue]

Joe,

If one pursues long enough one can overcome. Thanks for your post that said you would like to take a look at my database and code. I kept at it and eventually found that it was syntax, not diagnosed at report design time, but found at execution time, that was my original problem why I could not use IIF and SUM functions simultaneously.

My original syntax was of the form:

IIF ([SUM (fieldname) ] = 0, 0 , .....

which caused execution time prompts for the fieldname. All attempts to further "Define" the field with additional qualifications were fruitless.

When I finally (50 design changes later) tried:

IIF ( SUM ( [Fieldname] ) = 0, 0, ...

It worked as I wanted, and as it should.

I have learned. Thanks.
xbigblue