Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

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

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
wlwebb
Asked:
wlwebb
1 Solution
 
peter57rCommented:
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
 
wlwebbAuthor Commented:
Thanks Peter that worked with some modification
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now