# Thread: Report Totals (IIf) (2002 SP-2)

1. ## Re: Report Totals (IIf) (2002 SP-2)

The expression [txtOrderStatus]="BOL" is either True = -1, if the status is equal to "BOL", or False = 0 if it isn't. If 4 out of a total of 17 records have status equal to "BOL", the expression will equal -1 four times, and 0 the other 13. So the sum of the expression over all records will be -4. If we omit the - sign, we get exactly the count of records with status equal to "BOL".

So if you put a text box in the report footer with control source =-Sum([txtOrderStatus]="BOL"), it will display the count of records whose status equals "BOL". You can also use =Abs(Sum([txtOrderStatus]="BOL")) to get the same result. Similarly for other values of the status field.

2. ## Report Totals (IIf) (2002 SP-2)

<P ID="edit" class=small>(Edited by bfxtrfcmgr on 17-Feb-04 17:46. Picture removed. Forgot to delete sensitive info.)</P>(. . .the customer is always right, the custo. . .)
After putting together a special report for a customer, I now find that they want me to come over and read it for them too. Report totals have always been a bit of a challenge for me, but this one really has me stumped (I'll try to attach a picture, but I don't know if it will display very well). The report detail has a "status" txtbx that can reveal up to 18 different status' the order may be in. There is currently a txtbx showing the total number of orders in the report (form footer). Now they want a total for each different status to appear in the footer (no, not by something reasonable like "groups). At this point I can't even manage to enter a total for one status (keeps returning total for all status') so I'm pretty screwed up right now. (i.e. =IIf ([txtOrderStatus]="BOL", Count[txtOrderStatus],0). Any help greatly appreciated.

3. ## Re: Report Totals (IIf) (2002 SP-2)

Hans:

Boy, did I muck that up or what? Thanks for the solution (totally hadn't realized that about True/False). I guess I'm stuck with having to put all 18 txtbx in the footer, what with not knowing which status an order might be in at any given time. I notice there is no "Event" tab for a txtbx, so then I imagine there would be no way to "hide" txtbx that have no value? (or, rather, have a value of 0).

4. ## Re: Report Totals (IIf) (2002 SP-2)

Text boxes don't have events in a report, but what you need is the On Format event of the report footer section. Let's say that the text box displaying the count of records with status "BOL" is named txtCountBOL.

Private Sub ReportFooterSection_Format(Cancel As Integer, FormatCount As Integer)
Me.txtCountBOL.Visible = (Me.txtCountBOL > 0)
' similar for other text boxes
End Sub

(The report footer section may have a different name in your report.)

If you set the Can Shrink property of the text boxes and of the report footer section to Yes, and take care that there is no vertical overlap between the text boxes, empty lines will be suppressed.

5. ## Re: Report Totals (IIf) (2002 SP-2)

Hans:

<img src=/S/munch.gif border=0 alt=munch width=19 height=17>. . .digesting all that info.
That sounds really awesome! I need to get some other work completed, but will get back with the results on this. Thanks!!

6. ## Re: Report Totals (IIf) (2002 SP-2)

WOW! That's amazing!. . .where's the fireworks smilie?
(never could have done this with a wizard or a macro) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
Thanks "professor"! Some people are going to be very impressed when they open their e-mail attachment in the morning! I just love it when you make me look like a hero.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•