Want to display the Amount total in the Page Footer

[rajudh]

Hello,


I have created a report which print Invoice Form which Record Source is a query.
There are 3 Text box that shows sub total. round off and total amount at report footer.
But if there are one or two items it prints just below that items. I want to print these 3 text boxes at bottom of page.
If I put them at report footer it shows - "# error". Is there any way to print these 3 Text Box at end of page as in second image ?
The control source are -
TxtSubTotal =Sum([Quanity]*[Rate]),
TxtRoundOff = =[TxtTotal]-[TxtSubTotal],
TxtTotal = =Round(Sum([Quanity]*[Rate]),0.5)
I am attaching images for reference

Thanks.

[June7]

You said you get #Error in Report footer - did you mean Page footer? That would be expected for Page footer.
These calcs should work in Report footer but will not be at bottom of page. Add controls in Page footer that reference Report footer controls (these can be not visible).
If you want to provide db for analysis, follow instructions at bottom of my post.

Is it possible report could be multi-page? Do you want total just for items on each page or do you want total to be cumulative?
If you want aggregate calc for each page, this requires VBA code.
Place textboxes in Page footer then code behind report would be like:

Option Compare Database
Option Explicit
Public intPageCount As Integer
Public intPageTotal As Integer
______________________________________________________________________

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   ' increment intPageCount and intPageTotal for each record printed
    intPageCount = intPageCount + 1
    intPageTotal = intPageTotal + Nz(Me!Qty, 0)
End Sub
______________________________________________________________________

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    'set the textboxes in footer
    Me.tbxPageCount = intPageCount
    Me.tbxPageTotal = intPageTotal
    'reset our counters
    intPageCount = 0
    intPageTotal = 0
End Sub

[jojowhite]

you can also use this on Page footer:

TxtSubTotal
=DSum("Quantity*Rate", "YourTableName", "InvoiceNo = " & InvoiceNo)


TxtTotal
=Round(DSum("Quantity*Rate", "YourTableName", "InvoiceNo = " & InvoiceNo), 0.5)


TxtRoundOff = =[TxtTotal]-[TxtSubTotal],
​

[rajudh]

Thanks JojoWhite

This solve my problem

Thank you once again.