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
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?
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.
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.
NeoPa 32,556
Expert Mod 16PB
When attaching your work please follow the following steps first : - 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.
- 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.
- Compact the database.
- Compress the database into a ZIP file.
- 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.
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.
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.
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
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
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
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 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.
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.
@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
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.
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
@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
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.
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.
NeoPa 32,556
Expert Mod 16PB
Always a pleasure Adam :)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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!!
|
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...
|
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...
|
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...
|
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...
|
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.
|
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.
|
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
|
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-...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |