When we use formulas on textboxes in the report footers of MS Access reports, such as “=sum(a_field_in_header)”, we get incorrect results because of duplication of same amounts for multiple items in detail section.
Here is an example to make it clear:
We have two tables one for header and another detail records, for example, invoice header and a item details.
SELECT h.*, d.item, d.price, d.quantity, d.detail_subtotal, d.detail_vat, d.detail_total FROM t_trans AS h INNER JOIN t_trans_detail AS d ON h.trans_id = d.trans_id;
If we design a report to show the transactions:
We will get a listing of grouped transactions like this:
The report footer shows the problem and solution includes some VBA code below to do the correct calculations.
Option Compare Database Option Explicit Dim myTrans As New Collection Dim mySums(1 To 2) As Double '1 sum of total cost '2 sum of grandtotal 'distinct transactions Private Sub myCalculation(lTransID As Long, myCost As Currency, myTotal As Currency) On Error Resume Next myTrans.Add lTransID, "T" & lTransID 'try to insert transaction ID with collection Key If Err Then 'exists Err.Clear Else 'first time, so consider distinct headers mySums(1) = mySums(1) + myCost mySums(2) = mySums(2) + myTotal End If End Sub Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Debug.Print "Trans ID: " & Nz(Me.trans_id, 0) 'you will see this subroutine is called more than once Call myCalculation(Me.trans_id, Nz(Me.txt_totalcost, 0), Nz(Me.txt_grandtotal, 0)) End Sub Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer) Me.txt_sum_totalcost = mySums(1) Me.txt_sum_grandtotal = mySums(2) End Sub