Solved

Access - SubForm based on Query - Sum if query Is Null

Posted on 2013-01-31
2
520 Views
Last Modified: 2013-02-02
Hello all
I have a Form.  On that Form I Have a textbox where I display an amount from a formula that pulls totals from 3 Subforms.  

The 3 Subforms Each have a textbox in the header where I Sum([TrAmt]) .  Each Subform is based upon a different query.

Everything works right until in that Main Form textbox on the Main Form UNLESS one of the SubForm's queries is Null.  


I have tried nz(Sum([TrAmt],0) but if the Subform's query is null that textbox field just displays as blank.   Then the Main Form's textbox goes into an #Error state.

Any Suggestion?
0
Comment
Question by:wlwebb
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 38843028
NZ() won't have any effect if the value does not exist.

Try this approach...
Put a hidden textbox on the subform, and set its controlsource to:
 =Count(somefieldwhichwouldneverbenull)
Say this is called Kount

Then in the main form you can do...

=IIf([subctrl1].[Form]![Kount]>0,[subctrl1].[Form]![thesumcontrol],0)
0
 

Author Closing Comment

by:wlwebb
ID: 38847960
Thanks Peter that worked with some modification
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
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 …

789 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