Calculating Values in a Report After Each Grouping

[f15e]

I am not new to Access but am with the Reporting part of it. I have a specific calculation, having to do with equipment weights, that will be displayed after each grouping in the group footer. My calculation consists making 5 queries, per group, summing each of the 5 queries (which are called my dry equipment weights), taking the difference between the dry equipment weight and operational equipment weight and using the resulting value in a specific calculation. The final value from the specific calculation is to be displayed after each group.

Since this is not just a simple add / subtract calc where I could just put '= A + B' in a text box control, I created a subroutine to do all of this. The problem is I am not sure how to correctly call the subroutine after each grouping and put the values in the textboxes located in the group footer. What event would I call my subroutine from? I've tried the On Page, Current, On Focus, etc. The closest I got was the On Page event but get error '6' overflow after the 6th page. The variables I am using are doubles and tried changing them to single, variant, and also tried encasing with Nz but no luck. Below is the subroutine I'm trying to call and display in Group Footer after each grouping:


'************************************************* ************************************
' This sub needs to be called for each grouping and display
' in the textbox controls located in the Group Footer
Public Function Gross_Summ_Contingency_Calcs()

Dim inp_opWt_sum, inp_fut_opWt_sum, lout_dryWt_sum, trans_dryWt_sum, lift_dryWt_sum As Double
Dim inp_contin_calc, inp_fut_contin_calc, lout_contin_calc As Double
Dim trans_contin_calc, lift_contin_calc As Double

Dim sum_of_inp, sum_of_inp_fut, sum_of_lout, sum_of_trans, Sum_Of_LIFT As Double

inp_opWt_sum = 0
inp_fut_opWt_sum = 0
lout_dryWt_sum = 0
trans_dryWt_sum = 0
lift_dryWt_sum = 0

inp_contin_calc = 0
inp_fut_contin_calc = 0
lout_contin_calc = 0
trans_contin_calc = 0
lift_contin_calc = 0

sum_of_inp = 0
sum_of_inp_fut = 0
sum_of_lout = 0
sum_of_trans = 0
Sum_Of_LIFT = 0

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Weight_Data_Tbl")

' Each table record is falls into 1 of the 5 criteria cases below.
' Loop thru table and sum each case.
Do While Not rs.EOF ' loop through all records
With rs ' current RecordSet

' 5 criteria cases
If ((![TIME] = "AI" Or ![TIME] = "AL") And (![AREA] = Me.AREA.Value And ![DISCIPLINE] = Me.DISCIPLINE.Value)) Then
inp_opWt_sum = ![OPERATIONAL WEIGHT (sT)].Value + inp_opWt_sum
End If

If ((![TIME] = "AI" Or ![TIME] = "AL" Or ![TIME] = "FT") And (![AREA] = Me.AREA.Value And ![DISCIPLINE] = Me.DISCIPLINE.Value)) Then
inp_fut_opWt_sum = ![OPERATIONAL WEIGHT (sT)].Value + inp_fut_opWt_sum
End If

If ((![TIME] = "AL" Or ![TIME] = "LO" Or ![TIME] = "PS" Or ![TIME] = "TL") And (![AREA] = Me.AREA.Value And ![DISCIPLINE] = Me.DISCIPLINE.Value)) Then
lout_dryWt_sum = ![DRY WEIGHT (sT)].Value + lout_dryWt_sum
End If

If ((![TIME] = "AL" Or ![TIME] = "PS" Or ![TIME] = "TF" Or ![TIME] = "TL" Or ![TIME] = "TR") And (![AREA] = Me.AREA.Value And ![DISCIPLINE] = Me.DISCIPLINE.Value)) Then
trans_dryWt_sum = ![DRY WEIGHT (sT)].Value + trans_dryWt_sum
End If

If ((![TIME] = "AL" Or ![TIME] = "LF" Or ![TIME] = "PS" Or ![TIME] = "TF") And (![AREA] = Me.AREA.Value And ![DISCIPLINE] = Me.DISCIPLINE.Value)) Then
lift_dryWt_sum = ![DRY WEIGHT (sT)].Value + lift_dryWt_sum
End If

rs.MoveNext ' move to next record in Weight_Data_Tbl

End With
Loop

rs.Close

' I know I don't need to do this and could have just put it in the
' calcs below but I was just testing here to see if the overflow error
' had to do with putting the control value in the equations
sum_of_inp = Me.Sum_Of_IN_PLACE.Value ' NOTE: After the 6th page, the overflow error appears here.
sum_of_inp_fut = Me.Sum_Of_IN_PLACE___FUTURE.Value
sum_of_lout = Me.Sum_Of_LOAD_OUT.Value
sum_of_trans = Me.Sum_Of_TRANSPORT.Value
sum_of_lift = Me.Sum_Of_LIFT.Value


inp_contin_calc = ((sum_of_inp - inp_opWt_sum) / inp_opWt_sum) * 100
Me.inp_contin_summ.Value = Format(Round(inp_contin_calc, 1), "#,##0.0") ' display formatted value in textbox control

inp_fut_contin_calc = ((sum_of_inp_fut - inp_fut_opWt_sum) / inp_fut_opWt_sum) * 100
Me.inp_fut_contin_summ.Value = Format(Round(inp_fut_contin_calc, 1), "#,##0.0") ' display formatted value in textbox control

lout_contin_calc = ((sum_of_lout - lout_dryWt_sum) / lout_dryWt_sum) * 100
Me.lout_contin_summ.Value = Format(Round(lout_contin_calc, 1), "#,##0.0") ' display formatted value in textbox control

trans_contin_calc = ((sum_of_trans - trans_dryWt_sum) / trans_dryWt_sum) * 100


Me.trans_contin_summ.Value = Format(Round(trans_contin_calc, 1), "#,##0.0") ' display formatted value in textbox control

lift_contin_calc = ((Sum_Of_LIFT - lift_dryWt_sum) / lift_dryWt_sum) * 100
Me.lift_contin_summ.Value = Format(Round(lift_contin_calc, 1), "#,##0.0") ' display formatted value in textbox control

End Function
'************************************************* ************************************


I'd greatly appreciate any help you could provide. Thank you!

[f15e]

Ok so I am changing my thought process about this. I will create a query to sum the particular rows I need and put the query in the calculation set in my textbox control source.
Example:
(X_Value - query_name) / query_name * 100 <--- expression will be in control source for the textbox that will display this value

That should work correct? But the only other question I have is can I reference a value in my Report (a textbox with summed data) in the Query I create or do I need to use my table to get this summed value? I want to use the this textbox value in my Report in the WHERE clause of my Query.

[Micron]

Not sure you are not making this more complicate than it needs to be. If all the values needed do exist in the report's underlying query, why not just use a calculated control to perform the calculation and display this over the group? If they don't, I think it would be much easier if they did and they don't have to be visible.

Another possibility is to consolidate records into one final query, even if it means two or more sub queries are used as data sources instead of tables. That way you can build up to the desired results with calculated fields in one or more of your queries. This would allow you to have the aforementioned calculation in the group.

If you must run calculation code in a report group, it would probably have to be the OnFormat event. Another possibility is the Retreat event, but know that using either of these can cause noticeable increases in the time it takes to finish rendering the report. Also, IIRC, these events sometimes have no impact if the display mode is Report view as opposed to Print Preview.

[f15e]

You're correct. I was making it more difficult than it should have been. There is a lot of data to evaluate and I just noticed that there are cases when there's a sum of zero within the group and I was dividing by zero but initially the error produced didn't give me any indication that I was dividing by 0. I did some debugging exercises and that's when an error message popped up telling me 'dividing by zero'. It's now working as expected. Thanks for the reply.