The problem:
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
Categories:
Davit
Thanks, your article helped me a lot 🙂