473,322 Members | 1,405 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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 "SumOfTransactions", 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([SumOfTransactions])

... 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
Oct 18 '09 #1

✓ answered by 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

19 3949
NeoPa
32,556 Expert Mod 16PB
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?
Oct 18 '09 #2
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 "TransWithdraw"

The GROUP BY query sums "TransWithdraw" and creates a new field called "SumOfTransWithdraw".

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

(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([SumOfTransWithdraw])

It is the Text8 control that produces the #ERROR.
Oct 18 '09 #3
NeoPa
32,556 Expert Mod 16PB
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.
Oct 18 '09 #4
NeoPa
32,556 Expert Mod 16PB
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.
Oct 18 '09 #5
Ok, here is the DB, Access 2007

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

Thanks for the help.
Attached Files
File Type: zip CopyExpenseTracker1.zip (64.3 KB, 95 views)
Oct 18 '09 #6
NeoPa
32,556 Expert Mod 16PB
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.
Oct 19 '09 #7
ChipR
1,287 Expert 1GB
Create a query based on your Group By query with a field like
Expand|Select|Wrap|Line Numbers
  1. TransactionsTotal: Nz([SumOfTransactions], 0)
Then, use that field in your report and just
Expand|Select|Wrap|Line Numbers
  1. =Sum(TransactionsTotal)
Oct 19 '09 #8
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
Oct 20 '09 #9
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
Attached Files
File Type: zip ErrorDb.zip (11.6 KB, 101 views)
Oct 20 '09 #10
ajalwaysus
266 Expert 100+
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
Oct 20 '09 #11
NeoPa
32,556 Expert Mod 16PB
@AdamOnAccess
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.
Oct 20 '09 #12
NeoPa
32,556 Expert Mod 16PB
@ajalwaysus
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.
Oct 20 '09 #13
ajalwaysus
266 Expert 100+
@NeoPa
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
Oct 20 '09 #14
NeoPa
32,556 Expert Mod 16PB
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.
Oct 20 '09 #15
NeoPa
32,556 Expert Mod 16PB
@ajalwaysus
You mean you don't use an Access database to store all your references and links etc? That must be fun :D
Oct 20 '09 #16
ajalwaysus
266 Expert 100+
@NeoPa
I live in access so much day to day as it is, and I love it's flexibility, but that is just a little much. I gotta trust my mental memory with some information. =D

-AJ
Oct 20 '09 #17
NeoPa
32,556 Expert Mod 16PB
If I didn't have my database (or my text document before that) for those oft repeated postings I think I would have lost enthusiasm a long time ago. Also reformulating all the links would be a right royal PitA.

Being a moderator effects that a lot though. Most of the standard postings are moderation of some kind I suppose.
Oct 20 '09 #18
AJ

That's it! I'm very familiar with making forms, but clearly I need to learn more about reports. What bugs me is that with all the books I have here on Access, I couldn't find anything that describes this situation. I know I can't be the first guy to make this mistake.

Anyway, NeoPa, AJ, Thank you both.
Oct 20 '09 #19
NeoPa
32,556 Expert Mod 16PB
Always a pleasure Adam :)
Oct 20 '09 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: gooday | last post by:
Table test2 has multiple amounts for each account, I would like to sum the amounts for the same account and use the result to update the variable 'tot_amount' in table test1. But SQL does not allow...
1
by: Stingray Lien | last post by:
Dear all: How can I depend on field.value to control field.backcolor? which event I should use? I appreciate for your help!!
2
by: Scott Cannon | last post by:
I am trying to query 3 tables all related by Clinet_ID. The Clients table, Monthly_Expenses table and Monthly_Income table. Each client can have 0>M instances of expenses, past due expenses, and...
1
by: peter pilsl | last post by:
I have a question to sql and dont know exactely if this is the right place to ask. If not I would be glad if anyone could point me to the correct group/list/resource. Thnx. I want to perform a...
2
by: arvindpraj | last post by:
Hi, When i used sum() function in the SQL queries in MS Access for calculating the age of people in a database. i can able to get the result but the result comes out with a minus (negative) sign...
3
by: adnanahmed714 | last post by:
i am trying SQL Sum() function in vb to get sum of one database column but not getting result. select Sum(columnname) from emp. help me i want sum value to display in textbox. also i...
6
by: jhayes7781 | last post by:
I am trying to add the values of two fields in one table. Not sure how to get the SUM function to make this work.
1
by: jhayes7781 | last post by:
I am trying to add the values of two fields in one table. Not sure how to get the SUM function to make this work.
0
by: parallax | last post by:
Hi guys, I am trying to sum the values of two fields in 2 tables check by the name of the people in mysql, i'm not sure how to get the SUM function to make this work. Danylo
13
neo008
by: neo008 | last post by:
Hi All, How to run sum() Function in VB6? Adodc1.recordsource="select sum(quantity) from purchase" adodc1.refresh 1.) but how to retrieve result in desired text box like i took-...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.