I have Total_Scrap as a TextBox on footer1. It gets its value by SUM(Max Scrap)-(Min Scrap). I tried to redo the calculation in Footer 2 Total_Scrap_Shift but no dice. I would like to do it via code and need several more levels after shift I will SUM the Day then the week and so on. Any thoughts
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do you SUM a calculated Box?
- Thread starter DaveMac
- Start date
Hi DaveMac,
Your question isn't very clear, but I assume you're trying to produce a report with sorting and grouping.
You say SUM(Max Scrap)-(Min Scrap). Maybe this should read SUM([Max Scrap])-SUM([Min Scrap]). If you copy this formula, the one that works down to your various other footers, this should give you the info you're after. If not let me know, but with a bit more detail.
Bill
Your question isn't very clear, but I assume you're trying to produce a report with sorting and grouping.
You say SUM(Max Scrap)-(Min Scrap). Maybe this should read SUM([Max Scrap])-SUM([Min Scrap]). If you copy this formula, the one that works down to your various other footers, this should give you the info you're after. If not let me know, but with a bit more detail.
Bill
- Thread starter
- #3
Bill,
Thanks for the help. Yes I was shortcutting the first time here is what I have:
Private Sub Machine_Footer_1_Format (Cancel As Integer, FormatCount As Integer)
Me!Event_Total_Good = Me!Max_Running_Total_Scrap – Me!Min_Running_Total_Scrap
End Sub
Private Sub Shift_Footer_1_Format (Cancel As Integer, FormatCount As Integer)
Me!Shift_Total_Scrap_By_Machine = Sum([Max_Running_Total_Scrap] –[Min_Running_Total_Scrap]
End Sub
I am new to writing VBA and am thinking I would like to store the value form the calculation in Machine_Footer_1_Format the value of Me!Event_Total_Good and use it again in the next footer.
Thanks,
Dave
Thanks for the help. Yes I was shortcutting the first time here is what I have:
Private Sub Machine_Footer_1_Format (Cancel As Integer, FormatCount As Integer)
Me!Event_Total_Good = Me!Max_Running_Total_Scrap – Me!Min_Running_Total_Scrap
End Sub
Private Sub Shift_Footer_1_Format (Cancel As Integer, FormatCount As Integer)
Me!Shift_Total_Scrap_By_Machine = Sum([Max_Running_Total_Scrap] –[Min_Running_Total_Scrap]
End Sub
I am new to writing VBA and am thinking I would like to store the value form the calculation in Machine_Footer_1_Format the value of Me!Event_Total_Good and use it again in the next footer.
Thanks,
Dave
I can't really help you with the code here because I've only ever used a report's on format event to run procedures. It seems to me that this report is going to be a standard one, so I don't think there is any need for coding.
In your first footer which I think is Machine, add a text box or 2 text boxes, one upon another if you want both running sum over group totals and running sum over all totals, put your formula in the control source, which should be =SUM([Field1])-SUM([Field2])(type the field names as they appear in the table, query or recordset. In the running sum property, select over group or over all. Then just copy the control(s) to each of the other footers. There's really no need to name the controls in the standard way as I don't believe any coding will ne necessary. As long as you've done your sorting and grouping properly your figures should come out just fine.
If I've misread what you want to do or this doesn't work, post the exact names of the fields you want to use and a list of records, in order of the grouping, e.g. Machine, Shift, Day.
Regards
Bill
In your first footer which I think is Machine, add a text box or 2 text boxes, one upon another if you want both running sum over group totals and running sum over all totals, put your formula in the control source, which should be =SUM([Field1])-SUM([Field2])(type the field names as they appear in the table, query or recordset. In the running sum property, select over group or over all. Then just copy the control(s) to each of the other footers. There's really no need to name the controls in the standard way as I don't believe any coding will ne necessary. As long as you've done your sorting and grouping properly your figures should come out just fine.
If I've misread what you want to do or this doesn't work, post the exact names of the fields you want to use and a list of records, in order of the grouping, e.g. Machine, Shift, Day.
Regards
Bill
- Thread starter
- #5
Bill,
I think I have to use VBA. The wizard will not allow a stacking of functions. You see I first determine the Min and Max. So in my Machine_Footer_1 I have Max_Running_Total_Scrap and it is Max([Scrap]) the Same for Min. So to redo that calculation I would have to first evaluate the min and max then do the math and the Wizard must build some kind of SQL statement that so thanks but no thanks to my statement with multiple Aggregate functions. See I what is below makes any cense ?
Private Sub Machine_Footer_1_Format (Cancel As Integer, FormatCount As Integer)
Dim A Variable_To_Hold_My_Max_Scrap as Variant
Dim A Variable_To_Hold_My_Min_Scrap as Variant
Me!Max_Running_Total_Scrap = Max([Max Scrap])
Me!Min_Running_Total_Scrap = Max([Min Scrap])
Me!Event_Total_Good = Me!Max_Running_Total_Scrap – Me!Min_Running_Total_Scrap
End Sub
Private Sub Shift_Footer_1_Format (Cancel As Integer, FormatCount As Integer)
‘Somehow get the value From each Machine_Footer_1 down here and do the math
Me!Shift_Total_Scrap_By_Machine = Sum([Max_Running_Total_Scrap] –[Min_Running_Total_Scrap]
End Sub
I think I have to use VBA. The wizard will not allow a stacking of functions. You see I first determine the Min and Max. So in my Machine_Footer_1 I have Max_Running_Total_Scrap and it is Max([Scrap]) the Same for Min. So to redo that calculation I would have to first evaluate the min and max then do the math and the Wizard must build some kind of SQL statement that so thanks but no thanks to my statement with multiple Aggregate functions. See I what is below makes any cense ?
Private Sub Machine_Footer_1_Format (Cancel As Integer, FormatCount As Integer)
Dim A Variable_To_Hold_My_Max_Scrap as Variant
Dim A Variable_To_Hold_My_Min_Scrap as Variant
Me!Max_Running_Total_Scrap = Max([Max Scrap])
Me!Min_Running_Total_Scrap = Max([Min Scrap])
Me!Event_Total_Good = Me!Max_Running_Total_Scrap – Me!Min_Running_Total_Scrap
End Sub
Private Sub Shift_Footer_1_Format (Cancel As Integer, FormatCount As Integer)
‘Somehow get the value From each Machine_Footer_1 down here and do the math
Me!Shift_Total_Scrap_By_Machine = Sum([Max_Running_Total_Scrap] –[Min_Running_Total_Scrap]
End Sub
Hi again Dave,
It’s just dawned on me what you’re trying to do.
This is difficult to explain, but I’m going to have a go. Let’s say you have two different calculated text boxes in your report.
TextBox1’s ControlSource =Sum(Field1)-Sum(Field2) ‘this can be any calculation that you want
TextBox2’s ControlSource =Sum(Field3)-Sum(Field4) ‘this can be any calculation that you want
To Sum TextBox1 and TextBox2 :
TextBox3’s ControlSource=(Sum(Field1)-Sum(Field2))) + (Sum(Field3)-Sum(Field4))) ‘this is the sum of TextBox1 and TextBox2, adapt this to what you are trying to do.
Bill
It’s just dawned on me what you’re trying to do.
This is difficult to explain, but I’m going to have a go. Let’s say you have two different calculated text boxes in your report.
TextBox1’s ControlSource =Sum(Field1)-Sum(Field2) ‘this can be any calculation that you want
TextBox2’s ControlSource =Sum(Field3)-Sum(Field4) ‘this can be any calculation that you want
To Sum TextBox1 and TextBox2 :
TextBox3’s ControlSource=(Sum(Field1)-Sum(Field2))) + (Sum(Field3)-Sum(Field4))) ‘this is the sum of TextBox1 and TextBox2, adapt this to what you are trying to do.
Bill
- Status
Similar threads
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Helpful tip
