Subform Values

ExpandCollapse

Guest

Hello Everyone,
I have created a database with a number of subforms, each in form view,
which display appropriate calculated sums from a query. My problem lies in
that on one of my forms there are 6 tabs and to begin with all are empty.
Only over time will all be filled with data which will produce the necessary
sums. In the meantime I need to produce reports with the info from all six
categories. How can I get those subforms to display a zero untill data is
available. I can't make an Is Null argument work. Any thoughts? Thank you in
advance. Adam

ExpandCollapse

Guest

"F1stman" <F1stman@discussions.microsoft.com> wrote in message
news:674A1E53-3D9F-4FF3-BC1D-7EE9F789D158@microsoft.com
> Hello Everyone,
> I have created a database with a number of subforms, each in form
> view, which display appropriate calculated sums from a query. My
> problem lies in that on one of my forms there are 6 tabs and to begin
> with all are empty. Only over time will all be filled with data which
> will produce the necessary sums. In the meantime I need to produce
> reports with the info from all six categories. How can I get those
> subforms to display a zero untill data is available. I can't make an
> Is Null argument work. Any thoughts? Thank you in advance. Adam

It's not clear from your description how you have these subforms set up
and where you want the zeros to be captured or displayed, but the
following link may put you on the right track:

ExpandCollapse

Guest

Set the report control source to:

= Nz([YourSummaryExpression],0)

Sprinks

"F1stman" wrote:
> Hello Everyone,
> I have created a database with a number of subforms, each in form view,
> which display appropriate calculated sums from a query. My problem lies in
> that on one of my forms there are 6 tabs and to begin with all are empty.
> Only over time will all be filled with data which will produce the necessary
> sums. In the meantime I need to produce reports with the info from all six
> categories. How can I get those subforms to display a zero untill data is
> available. I can't make an Is Null argument work. Any thoughts? Thank you in
> advance. Adam

ExpandCollapse

Guest

Thank you both for the help. Unfortunately neither method is working. I can't
fiure out the proper place to put the code from Dirk and the nz statement
from Sprinks isn't working either.
To clarify I have a financial tracker with 6 tabs representing 6 accounts
per project. Each projects individual sales are tallied via a query and then
displayed via a subform on the appropriate tab. In each sub form only one
object is displayed (the total). I have a report that uses the information
from this form to create a financial exhibit. When there are no values, the
form displays a blank area and the report an error. I would like to get both
to display a zero. Any more help is appreciated or perhaps clarification on
the coding instruction given before. VBA rookie here.

"F1stman" wrote:
> Hello Everyone,
> I have created a database with a number of subforms, each in form view,
> which display appropriate calculated sums from a query. My problem lies in
> that on one of my forms there are 6 tabs and to begin with all are empty.
> Only over time will all be filled with data which will produce the necessary
> sums. In the meantime I need to produce reports with the info from all six
> categories. How can I get those subforms to display a zero untill data is
> available. I can't make an Is Null argument work. Any thoughts? Thank you in
> advance. Adam

ExpandCollapse

Guest

"F1stman" <F1stman@discussions.microsoft.com> wrote in message
news:A69DAB0A-7CED-4D68-8A90-44CB977E77D1@microsoft.com
> Thank you both for the help. Unfortunately neither method is working.
> I can't fiure out the proper place to put the code from Dirk and the
> nz statement from Sprinks isn't working either.
> To clarify I have a financial tracker with 6 tabs representing 6
> accounts per project. Each projects individual sales are tallied via
> a query and then displayed via a subform on the appropriate tab. In
> each sub form only one object is displayed (the total). I have a
> report that uses the information from this form to create a financial
> exhibit. When there are no values, the form displays a blank area and
> the report an error. I would like to get both to display a zero. Any
> more help is appreciated or perhaps clarification on the coding
> instruction given before. VBA rookie here.

You would paste the code from that link into a standard module. Then
you would wrap references to the troublesome controls in calls to the
nnz() function; e.g.,

=nnz([Forms]![YourForm]![YourSubform]![YourControl])

or
=nnz([Forms]![YourForm]!![YourControl])

depending on whether the control in question is on the subform or on the
main form.

But I still don't have a good understanding of the way you have this set
up. If you're trying to display these results in a report, it makes
little sense to refer to a form for the data -- that requires, for
example, that the form always be open when the report is run. Normally,
one would define the report's recordsource query so that it calculates
and returns all the information you need to display on the report.

Share This Page

The simple act of eating vegetarian food has become more political than it ever was. While it is lovely to be vegetarian, it is critical we ponder on why we want to be vegetarian: for health, to save the world, or to purify our soul. As spirituality-that-excludes becomes increasingly popular...

About Us

Our community has been around for many years and pride ourselves on offering unbiased, critical discussion among people of all different backgrounds. We are working every day to make sure our community is one of the best.

Like us on Facebook

Support SPN

The management works very hard to make sure the community is running the best software, best designs, and all the other bells and whistles. We'd really appreciate your support!