Solved

Access Continuous Form Subtotal Textbox #Error

Posted on 2014-02-18
5
1,483 Views
Last Modified: 2014-02-18
Hello experts!

I have a form with a subform.  The subform's info comes from a query.  In the Footer of the Subform I put an unbound textbox.

The continuous form's bound field is [AmtIn].  In the footer I put the formula
=nz(Sum([AmtIn]),0)

Everything works great EXCEPT when the query results in "No Records".  Then I get the dreaded #Error which throws every other calculated field on the Form to #Error.

Am I doing something wrong or is this a Access 2007 bug that was never fixed?
0
Comment
Question by:wlwebb
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39868276
Have you tried Sum(Nz([AmtIn],0))?

I think you need to do the Nz bit before the sum not after.

Kelvin
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39868277
How about basing the other controls which are based on the txt_Total control in the subform with a control source of something like:

=IIF(IsError(me.subformcontrol.form.txt_Totals), 0, me.subformcontrol.form.txt_Totals)
0
 

Author Comment

by:wlwebb
ID: 39868482
Kevin.

Doesn't work if Query has no records.
0
 

Author Closing Comment

by:wlwebb
ID: 39868517
Thanks Fyed.....  Works like a charm... Tried Iserror but my syntax was wrong.  Thank you
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39868541
glad to help.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

776 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