Detail Section: Format Property

[Knicks]

I have a report with 2 levels of groupings and in the detail section I have about 25 fields that can have a value of 'NFE'

The code I placed in the detail section looks at all the controls in the detail section and adds '1' evertime 'NFE' is a value. I then pass that value to an unbound text box on the detail line, [text113]

That portion works fine. But I am unable to sum that afterwards in any of the group footers. I've tried referring to the field and performing the sum 'over group' and also just using the Sum([text113]). Either I get unexpected results or it won't let me. I suppose what I am trying to do is close to a cross-tab query.

What would be the easiest way to now take those values from [text113] or the variable totalNSE and get sums in the 2 footers I have and then for the whole report.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim totalNSe As Integer
Dim totalSp As Integer
Dim totalSpT As Integer
Dim ctl As Control

totalNSF = 0
totalNse = 0

For Each ctl In Me.Detail.Controls
'Debug.Print ctl.Value

If ctl.Value = "nfe" Then
totalNSe = totalNSe + 1
'Debug.Print totalNSe
Else
End If

Next ctl
'Debug.Print totalNSe



Text113 = totalNSe

 

[dhookom]

First, change the name of text113 to txtTotNFEs. Then create two text boxes (txtGrpNFEs and txtAllNFEs) and set their control sources to:
=txtTotNFEs
Set their Running Sum properties to Over Group and Over All.
Then add text boxes to the group and report footers with Control Sources of:
=txtGrpNFEs
and
=txtAllNFEs
No promises but this might work. At least your text box will have a descriptive name ;-)

Duane
Hook'D on Access
MS Access MVP

 

[Knicks]

Thank you for trying,

I get some very odd numbers attempting that technique. Occassionally the numbers appear 'close' for the groupheader(0), they are completely off for the groupheader(1).

 

[dhookom]

I think you would have to use the Group Footer for these calcs. Did you try the footer sections as I suggested?

Duane
Hook'D on Access
MS Access MVP

 

[Knicks]

Yes, I used the group footers. The numbers rarely add correctly for the summing over the groups. And yet it adds perfectly across everytime.

 

[dhookom]

If you can't identify the inconsistency, you may have to create an expression outside of code and then sum it.

I just created a similar report and it worked exactly as I expected. I used code to fill an unbound text box and then added a couple text boxes with running sums and referenced them in the group and report footer sections. Everything totaled exactly as expected.

Duane
Hook'D on Access
MS Access MVP

 

[Knicks]

Thanx,

So in theory that should of worked.

Its about 26 fields and I just thought it would be the smart advanced way to handle it. I knew I could of used the query with either an IIF or ABS and then totaled there yielding a bound field. Seemed like the tedious way to handle it, but it may have to be.

 

[dhookom]

I don't know how your report doesn't work when mine does. I didn't have such a complex calculation but that shouldn't make a difference. My report is in the Northwind with code in the On Format of the Detail Section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtTotNFEs = Me.Quantity * Me.UnitPrice
End Sub

The remainder of the solution is how I explained previously.

You didn't use the Page Footer section did you?

Duane
Hook'D on Access
MS Access MVP

 

[Knicks]

No, just the group footer sections.

Your code doesn't loop through the controls though I suppose it replicates a similar situation.

 

[dhookom]

There is no such thing in Access reports as "very odd numbers". There is a perfectly good explaination for all numbers even though it might be difficult to determine.

Can you tell me how you implemented my solution?

Duane
Hook'D on Access
MS Access MVP