Display Calculation in Report Footer 1

[LarryDeLaruelle]

I am trying to count the number of fields in the detail band that contain a specific text string and then calculate the percentage that number represents of the total number of records.

I tried: =Count([FieldName] = String) and various other ways of setting up the expression and continued to get the total count of records (based on what I read in the Help files that would be correct).

Since I could not get this to work in an unbound text box, I set up accumulator variables in code behind the form: dimmed the variables in General Declarations, initialized them in the form's On Open event and put the IF/THEN criteria in the Detail Band On Format event. By using a break point I can tell that the accumulators are working correctly as are my percentage calculations.

What I cannot figure out is how to place the results of those calculation in the Report Footer. I've tried =[intName] and [Reports]![ReportName]![intName]. The only response I've gotten is a parameter prompt for a value for intName.

Am I approaching this from the right direction or am I off base? If yes, how should I be doing this; if no, what am I missing to make the results display properly?

Thanks.

 

[jimmythegeek]

I've used this before.

Put a label on your Report Footer, let's call it lblCount
Set the caption to "0"

Now on the Format Procedure of your Detail section, put the following:

If Me.YourField = "YourCriteria" Then
Me.lblCount.Caption = Me.lblCount.Caption + 1
End If

 

[LarryDeLaruelle]

Saved me again, Jim. Thank much.

I did as you suggested and it works like a dream and is also a lot less work that the way I was approaching it.

I had figured out to put text boxes in the footer and assign the accumulator values to them. That worked as far as getting the information to the report but, oddly, if the report exceeded one page it seemed to count the last record twice. Any clue as to what might have been causing this?

Thanks again.

 

[jimmythegeek]

You were in the Report footer, and not the Page footer, correct?

If so, I don't know for sure why it would do that. I would have to re-create it to find out why.

 

[LarryDeLaruelle]

I originally had the display fields in a group footer but dragged them to the report footer.

After dragging them down I still got the extra count.

Your suggestion is a more elegant solution since it requires much less work than the way I had originally set it up.

Thanks again.

 

[RYankowitz]

I had the same problem (with records counting twice when the report exceeded one page). It drove me crazy, to the degree of tearing out some of my remaining precious hair. I was heading off in the wrong direction - towards the Retreat event.

I finally found it. In cutting and pasting my code I noticed the "FormatCount" argument of the Format event, and realized this was probably what I had overlooked.

My understanding of the Retreat event appears woefully inadequate.

What is really happening is that Access can take care of totalling just fine, thank you, but if I have to do it myself I need to take special precautions, thusly:

[tt]
Private Sub PurchaseOrder_Detail_Format(Cancel As Integer,_
FormatCount As Integer)

If FormatCount <= 1 Then
SumOrderTotal = SumOrderTotal + Order_Total
SumAmountDue = SumAmountDue + AmountDue
SumCurrent = SumCurrent + Current
SumOne2Thirty = SumOne2Thirty + One2Thirty
.
.
.
SumNinetyOnePlus = SumNinetyOnePlus + NinetyOnePlus
End If

End Sub
[/tt]

Since some records are formatted twice (when Access realizes they won't fit on the bottom of a page), they can get added to the totals twice. The check for the FormatCount prevents that. Seems simple, once you know you need to do it.