Report Footer Giving Wrong Result (2000)

Topic

New Reply

Hi,

I’ve got a report that has a headers/footers by LastName/BusinessName and InvoiceNo. Everything is working fine with the exception the Total Amount Outstanding in the LastName/BusinessName footer. The result that it is giving is the TotalDue for the entire report, not just for that account. I’ve never had this happen before. I have many reports that have the Total by LastName….. in the corresponding footer and then the overall total in the report footer. I’m not sure what I’m doing wrong..

The forumula I’m using is ………..=Sum([InvoiceTotal])-Sum([AmtAlloc]). As I said, this works fine in the report footer but nut in the LastName/BusinessName footer.

Thanks,
Leesha

Reply | Quote

Replies

Leesha
It might help if we could see the report causing the problem.
Can you attach a small demo ?

Reply | Quote

Sure thing. Here you go.

Leesha

Reply | Quote

Hi Leesha…

When I tried to open the report in Preview mode, I got an error saying that there was a data type mismatch somewhere in the SQL statement for the report… Since the query itself opened up without error, I figured the problem was somewhere in the calculated fields in the report design… Therefore, the first thing I did was remove the expressions for the two totals fields in question… The report then opened in Preview fine…

After that, I changed three things…

1. changed the expression for Text104 (in LastName/BusinessName Footer) to “=Sum([InvoiceTotal]-[AmtAlloc])”
2. created a new text box named “txtGrandTotal” in the same area (in LastName/BusinessName Footer)
   • set the control source to “=[Text104]”
   • changed the RunningSum property to “Over All”
   • set the Visible property to False

Hi Trudi,

Thank you for your post. I’m having a devil of a day. I made all your changes but my numbers are still wrong. Would you mind posting back to me what you did so I compare it to mine and see where I’m going wrong. I’m not sure why you got error messages when the report opens as I don’t. Been a stumper of a day.

Thanks,
Leesha

Reply | Quote

Hi Leesha…

Sure… I’ll attach the database for you…
One thing though… I took a second look at my report… The numbers didn’t look quite right to me, so I played again…

Just to clarify… Here’s how I see it…
You are listing each invoice for each client…
The Total Invoice Balance Due is the total of each invoice, less what’s already been paid for that invoice…
The Total Amount Due is the total of all outstanding unpaid amounts for each client’s account…
The Total Outstanding Balance / Credit Due is the total outstanding unpaid amounts for the entire report…

Am I assuming correctly?? …If so, look at the report named “Fixed rptBillingHistory2″…

The changes I described in my prior reply are shown in “Fixed rptBillingHistory”… The new one has a couple more changes and another new text box… Sorry for the confusion… I don’t know where my head was when I fixed it the first time… It’s been one of those days for me too…

HTH… Have a great evening!

Reply | Quote

Hi Trudi,

Thanks so much for sending back the corrections you made. Your second version in fact is exactly what I needed. I figured it was just me doing something wrong. Not to worry re the numbers being wrong in the first version!

Thanks again, hope your day perks up,
Leesha

Reply | Quote