Solved

Access Continuous Form Subtotal Textbox #Error

Posted on 2014-02-18
5
1,457 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
Comment Utility
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
Comment Utility
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
Comment Utility
Kevin.

Doesn't work if Query has no records.
0
 

Author Closing Comment

by:wlwebb
Comment Utility
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)
Comment Utility
glad to help.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now