|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 16th, 2005, 11:49 PM
posted to microsoft.public.access.reports
|
|||
|
|||
Group footer shows zero values for sum when at top of page
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? -- Mr Antiphun 
|
|
#2
November 17th, 2005, 12:11 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Group footer shows zero values for sum when at top of page
Could you share your control source or your method for displaying sums?
-- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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? -- Mr Antiphun
|
|
#3
November 17th, 2005, 03:21 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Group footer shows zero values for sum when at top of page
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 -- Mr Antiphun "Duane Hookom" wrote: Could you share your control source or your method for displaying sums? -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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? -- Mr Antiphun
|
|
#4
November 17th, 2005, 04:05 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Group footer shows zero values for sum when at top of page
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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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 -- Mr Antiphun "Duane Hookom" wrote: Could you share your control source or your method for displaying sums? -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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? -- Mr Antiphun
|
|
#5
November 17th, 2005, 09:30 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Group footer shows zero values for sum when at top of page
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. -- Mr Antiphun "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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 -- Mr Antiphun "Duane Hookom" wrote: Could you share your control source or your method for displaying sums? -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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? -- Mr Antiphun
|
|
#6
November 17th, 2005, 10:10 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Group footer shows zero values for sum when at top of page
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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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. -- Mr Antiphun "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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 -- Mr Antiphun "Duane Hookom" wrote: Could you share your control source or your method for displaying sums? -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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? -- Mr Antiphun
|
|
#7
November 17th, 2005, 10:31 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Group footer shows zero values for sum when at top of page
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. -- Mr Antiphun "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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. -- Mr Antiphun "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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 -- Mr Antiphun "Duane Hookom" wrote: Could you share your control source or your method for displaying sums? -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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? -- Mr Antiphun
|
|
#8
November 18th, 2005, 12:08 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Group footer shows zero values for sum when at top of page
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". -- Mr Antiphun "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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. -- Mr Antiphun "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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 -- Mr Antiphun "Duane Hookom" wrote: Could you share your control source or your method for displaying sums? -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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? -- Mr Antiphun
|
|
#9
November 18th, 2005, 12:16 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Group footer shows zero values for sum when at top of page
Never mind, I got it. Thanks for the help.
-- Mr Antiphun "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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. -- Mr Antiphun "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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 -- Mr Antiphun "Duane Hookom" wrote: Could you share your control source or your method for displaying sums? -- Duane Hookom MS Access MVP -- "Mr Antiphun" wrote in message ... 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? -- Mr Antiphun
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How do i hide a group footer if it's the only group? | Darin | General Discussion | 7 | August 30th, 2005 02:45 PM |
| How do i hide a group footer if it's the only group? | Darin | Setting Up & Running Reports | 7 | August 30th, 2005 02:45 PM |
| Auto numbering | store5064 | General Discussions | 11 | June 2nd, 2005 08:38 PM |
| ambiguous outer joins | renwick | Running & Setting Up Queries | 3 | February 22nd, 2005 01:29 PM |
| Help Needed for Groups Please | Paul Black | General Discussion | 15 | June 21st, 2004 02:54 AM |
Linear Mode
