#Error in control with GroupBy and Sum([Field]) function

[AdamOnAccess]

I always seem to have trouble with calculated fields in a report.

Right now, I have a report that works like this...

I have a table containing individual transactions, and each transaction is related to a table called "Expenses". The "Expenses" table contains the categories for each transactions. I use a GROUP BY query to sum all the transactions into their respective expense categories.

I use this GROUP BY query as the source for my report. When you run the Group-By query, it produces a field called "SumOfTransacti ons", and that field appears in the details of my report. The detail of the report shows all the expense categories and their totals.

Now, at the end of the report, I want to get the total of all the expense categories. At the bottom of my report, I create a calculated field with the source:

=Sum(Nz([SumOfTransactio ns])

... and I get #Error.

I've checked my references and I'm certain I've named the field correctly.

I've tried running a SELECT query on the results of the GROUP-BY query, and then using the SELECT results as the source, but that didn't fix the problem. I assume there is a trick to totalling a GROUP-BY query, but I can't figure it out. Any help would be appreciated?

Thanks
-Adam

[NeoPa]

I suspect that you are actually referring to the Field, as the title claims. That would not work as it would need to refer to a control of the report.

Is that field displayed in a control in the Detail section? What is that Control called?

[AdamOnAccess]

Hi NeoPa,

Thanks for coming to my rescue... Again :)

Here's some info that may help you figure this out, because I certainly have no idea!

The Original Table contains the field "TransWithd raw"

The GROUP BY query sums "TransWithd raw" and creates a new field called "SumOfTransWith draw".

A control text box is placed in the detail section of the report:
Name: SumOfTransWithd raw
Control Source: SumOfTransWithd raw

(I did try changing the name of the control to see if that would help, but I still got the #ERROR")

In the page footer section of the report, another text box control:
Name:Text8
Control Source: =Sum(Nz([SumOfTransWithd raw])

It is the Text8 control that produces the #ERROR.

[NeoPa]

If it's not the missing closing parenthesis, then I'm not sure what's wrong.

I'll post some instructions on how to attach your database to the thread and I'll have a look at it for you.

[NeoPa]

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. If you've done anything in step 1 then make sure that the problem you're experiencing is still evident in the cut-down version.
3. Compact the database.
4. Compress the database into a ZIP file.
5. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

[AdamOnAccess]

Ok, here is the DB, Access 2007

When you open the database, open the form "fGroupTransact ions". This form contains date fields that the report needs for the query. Minimize the form, then open fDepositsWithra w. Look at the bottom of the page and you'll see #ERROR.

Thanks for the help.

[NeoPa]

Sorry Adam. I didn't notice the version number until after I tried (& failed) to look at it (otherwise I could have posted this earlier). I only have Access 2003 I'm afraid.

[ChipR]

Create a query based on your Group By query with a field like

TransactionsTotal: Nz([SumOfTransactions], 0)

Then, use that field in your report and just

=Sum(TransactionsTotal)

[AdamOnAccess]

NeoPa & ChipR

ChipR - Thanks for the idea, but I still can't get it to work.

NeoPa - Please keep this thread on your radar. I have versions of Access earlier than 2003. I feel I am doing something fundamentally wrong. I'm going to try and recreate the same error in an early version and I'll post in on this thread. Be back later.

-Adam

[AdamOnAccess]

Ok, I've managed to duplicate the error in Access 2000 format. I've kept it simple so you shouldn't have any problem taking it in.

There is only one report in the database. Just run it in preview, look at the bottom of the page, and you'll see #Error.

Thanks again for the help
-Adam

[ajalwaysus]

I hate access reports (not very flexible), and I hope I am not mentioning something that has already been said or ruled out, but your issue here is that you have the sum in the page footer, it needs to be in the report footer.

I hope I haven't over simplified this.

-AJ

[NeoPa]

NeoPa - Please keep this thread on your radar. I have versions of Access earlier than 2003. I feel I am doing something fundamentally wrong. I'm going to try and recreate the same error in an early version and I'll post in on this thread. Be back later.

I am subscribed to this thread Adam, just like all the others I'm involved in, so whenever you have something to post I will know that it's there.

[NeoPa]

I hope I haven't over simplified this.

I don't think you have AJ. If that is indeed so I would expect it to fail. The page after all, is not a higher level grouping section. The Sum() function would have no context within which to work.

[ajalwaysus]

The page after all, is not a higher level grouping section. The Sum() function would have no context within which to work.

That is exactly what I was thinking. Also this has taught me that this should be one of the first questions asked to anyone having this issue going forward, so I will put this in my memory banks. =)

-AJ

[NeoPa]

Is it a running sum that you require Adam, such that it can be printed on every page and reflect the running total? If so that would be done differently. You would need a hidden control in the Detail section which was bound to the value requiring totalling and had its .RunningSum property set to Yes.