Group footer shows zero values for sum when at top of page

[Guest]

An access 2002 report shows sum values of zero when the group footer prints
at the top of page. All other group footers show correct values. It is like
the sum values get cleared to early.

Any suggestions?

 

[Duane Hookom]

Could you share your control source or your method for displaying sums?

 

[Guest]

Here is the logic for the detail, group footer 1, and group footer 2.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case LB_TYPE

Case 1000 'general
giSum1000 = giSum1000 + NET_WT
gi2cSum1000 = gi2cSum1000 + NET_WT
giGtSum1000 = giGtSum1000 + NET_WT
Case 2000 'Custom added 8/13/2003 TJM
giSum2000 = giSum2000 + NET_WT
gi2cSum2000 = gi2cSum2000 + NET_WT
giGtSum2000 = giGtSum2000 + NET_WT
Case 4000 'Rag Sales added 5/25/2004 TJM
giSum4000 = giSum4000 + NET_WT
gi2cSum4000 = gi2cSum4000 + NET_WT
giGtSum4000 = giGtSum4000 + NET_WT
Case 5000 'surgical
giSum5000 = giSum5000 + NET_WT
gi2cSum5000 = gi2cSum5000 + NET_WT
giGtSum5000 = giGtSum5000 + NET_WT
Case 6000 'credit
giSum6000 = giSum6000 + NET_WT
gi2cSum6000 = gi2cSum6000 + NET_WT
giGtSum6000 = giGtSum6000 + NET_WT
Case 9000 'soiled
giSum9000 = giSum9000 + NET_WT
gi2cSum9000 = gi2cSum9000 + NET_WT
giGtSum9000 = giGtSum9000 + NET_WT
End Select

End Sub

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)

If giSum1000 <> 0 Then
Me.txtSoilCleanVar.Value = giSum9000 / giSum1000 'variance
Else
Me.txtSoilCleanVar.Value = 0
End If
giSum1000 = 0 'general
giSum2000 = 0 'Custom added 8/13/2003 TJM
giSum4000 = 0 'Rag Sales added 5/25/2004 TJM
giSum5000 = 0 'surgical
giSum6000 = 0 'credit
giSum9000 = 0 'soiled

End Sub


Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)

Me.txt2cClean.Value = gi2cSum1000 ' total general
Me.txt2cCustom.Value = gi2cSum2000 ' total Custom added 8/13/2003 TJM
Me.txt2cRagSales.Value = gi2cSum4000 ' total Rag Sales added 5/25/2004 TJM
Me.txt2cSurgical.Value = gi2cSum5000 ' total surgical
Me.txt2cCredit.Value = gi2cSum6000 ' total credit
Me.txt2cSoiled.Value = gi2cSum9000 ' total soiled

If gi2cSum1000 <> 0 Then
Me.txt2cSoilCleanVar.Value = gi2cSum9000 / gi2cSum1000 'variance
Else
Me.txt2cSoilCleanVar.Value = 0
End If

gi2cSum1000 = 0 'general
gi2cSum2000 = 0 'Custom added 8/13/2003 TJM
gi2cSum4000 = 0 'Rag Sales added 5/25/2004 TJM
gi2cSum5000 = 0 'surgical
gi2cSum6000 = 0 'credit
gi2cSum9000 = 0 'soiled

End Sub

 

[Duane Hookom]

I have created hundreds of reports and never had to create sums with code.
This type of code is almost guaranteed to create errors.

If you want to sum the NET_WT for records where LB_TYPE = 1000, use a text
box in a group or report header or footer with an expression:

=Sum(Abs(LB_TYPE=1000) * NET_WT)
If you have many LB_TYPES and they might change, you would be better off
creating a subreport.

 

[Guest]

Duane,

Thanks for the reply. Question: how does the ABS function filter all LB_TYPE
records to sum? Are you using this function simply as an example? I have
never used a selective sum without having to write code, so this concept is
new to me.

Also, the logic does work, it just doesn't show the correct total when the
group footer is forced to advance to top of page. If I force a new page
before and after, the totals show up, but I waste a lot of paper. I have used
both the "force new page after section" and "force new page none", but
whenever the group footer lands at top of page, the sums show up as zero.

 

[Duane Hookom]

Did you try my suggestion? Does my WAG at your needs mirror your situation?

I would never attempt to use code in a report to calculate a total.

If we were privy to your actual requirements, we could provide more
assistance.

The LB_TYPE=... returns either true/-1 or false/0. The Abs() function
converts the -1 to 1 so it can be multiplied by another number and summed.

 

[Guest]

Thanks for the suggestion. I will try it again. I also see what you mean by
using subreports. I am in the process of trying that.

If I have more questions, I will let you know.

I believe I am on the right road.

 

[Guest]

Duane,

I can get the =Abs([LB_TYPE]=1000)*[NET_WT] to work on a detail line, but if
I do a =Sum(Abs(LB_TYPE=1000) * NET_WT) in the group footer, I get the error
"Data type mismatch in criteria expression".

 

[Guest]

Never mind, I got it. Thanks for the help.