[Last Call] Learn how to a build a cloud-first strategyRegister Now


MS Access #Error in Report with Query Source in Calculating Currencty Avg, Queried by Date Parameters

Posted on 2007-10-07
Medium Priority
Last Modified: 2013-11-28

I'm using MS Access 2003. I've created a query with date parameters to give me the AppWorth values for Donations from a Table name DonationTbl. The name of the query is WeeklyDonationsAvg and the name of the report is WeeklyDonationsAvgRpt. I am trying to calculate the Average of the AppWorth in the Page Footer of the report.

I get a #Error when I try to run the report using =Avg([AppWorth]) or using Sum("[AppWorth]")

No matter how I put it, I still get the #Error. Both AppWorth is Currency Format and so is the label, which is label12.

Thanks again, for your assistance..

Question by:ancrumc
  • 3
  • 2
  • 2
  • +1
LVL 66

Expert Comment

by:Jim Horn
ID: 20030080
There may be some AppWorth values which are NULL, which would throw off any aggregate function such as Sum, Avg, etc.

Try =Sum(Nz(AppWorth],0)

or even better change your query such that AppWorth is Nz(AppWorth, 0)

Author Comment

ID: 20030101
AppWorth in the Query is data that comes directly from a table, so there are no calculations to get the AppWorth. It is directly typed into the Table.

I tried =Sum(Nz([AppWorth]),0) and it doesn't all that syntax, it says that is invalid.

The Query looks like this
AppWorth             DDate                DonationID
DonationTbl          DontationTbl
LVL 77

Expert Comment

ID: 20030154
It is never easy putting calculations into a page footer.
If possible do it somewher else (any other footer is preferable).

There is an article here which shows how to calculate a sum in the page footer.
You can expand the code to give you a sum and a count and do the avarage calc yourself.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 66

Expert Comment

by:Jim Horn
ID: 20030156
Then modify your query, changing out AppWorth with something like this:

SomeOtherName:  Nz(AppWorth, 0)

Then use SomeOtherName in your report.
LVL 77

Accepted Solution

peter57r earned 600 total points
ID: 20030157
The article I missed out...
LVL 75
ID: 20030552
Make sure the text box containing

=Avg([AppWorth]) or using Sum("[AppWorth]")

has a different name than 'AppWorth' eg, txtAppWorth


Author Comment

ID: 20030585
Thanks everyone, but so far nothing has worked...I tried the article and use the VB Code; however, it asks for the Page Sum parameter, when I open the report and if I put Zero then, the Sum returns the value 0.

I'm going to keep searching and trying to figure it out...If someone figures it out before me, I welcome your assistance. Thanks again...

Author Comment

ID: 20030746
I figured it out...I calculated the Average in the Detail Section of the report.

I hid the Average Duplicates and the label and text box altogether, and then I created another text box that displays the value of the Average in the Page Header Section.

Works Great!

Thank you all again...

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question