Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Null records give Count - #error - is there a way to fix?

Discussion in 'Information Technology' started by Heidi, Nov 9, 2005.

  1. Heidi

    Heidi
    Expand Collapse
    Guest

    I have a subreport that has a count for the number of names in it. I
    reference this count in the main report and add it to the count there. My
    problem is that sometimes the sub doens't have any data - i've tried IIf, Nz
    and cant seem to come up with anything that will let me have the value be
    zero if it's null instead of coming up #error. Does anyone know?

    Thanks!
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh woman's forced marriage annulled in Britain (New Kerala) Breaking News Oct 22, 2008
    Sikh News Sikh woman’s marriage annulled in UK (Hindustan Times) Breaking News Oct 22, 2008
    Concept Of Sunn-Void,nullity, Essays on Sikhism Jan 16, 2008
    Sikh News UK Sikh Harnaam Kaur Enters Guinness Records As Youngest Female With Beard - The Indian Express Breaking News Sep 9, 2016
    USA Robbery suspect seeks his NSA records Breaking News Jun 13, 2013

  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Heidi" <Heidi@discussions.microsoft.com> wrote in message
    news:C540F095-A240-4DFE-ACFF-B3C9B6271E59@microsoft.com
    > I have a subreport that has a count for the number of names in it. I
    > reference this count in the main report and add it to the count
    > there. My problem is that sometimes the sub doens't have any data -
    > i've tried IIf, Nz and cant seem to come up with anything that will
    > let me have the value be zero if it's null instead of coming up
    > #error. Does anyone know?


    Keri Hardwick's nnz function, posted here ...

    http://www.mvps.org/access/forms/frm0022.htm

    .... should solve your problem.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. Heidi

    Heidi
    Expand Collapse
    Guest

    This is a report though so I can't get it to pass thru the code (tried
    watches and breakpoints to check - doesn't even touch it). Doesn't fix the
    problem..... tried it with the name of the count field....



    "Dirk Goldgar" wrote:

    > "Heidi" <Heidi@discussions.microsoft.com> wrote in message
    > news:C540F095-A240-4DFE-ACFF-B3C9B6271E59@microsoft.com
    > > I have a subreport that has a count for the number of names in it. I
    > > reference this count in the main report and add it to the count
    > > there. My problem is that sometimes the sub doens't have any data -
    > > i've tried IIf, Nz and cant seem to come up with anything that will
    > > let me have the value be zero if it's null instead of coming up
    > > #error. Does anyone know?

    >
    > Keri Hardwick's nnz function, posted here ...
    >
    > http://www.mvps.org/access/forms/frm0022.htm
    >
    > .... should solve your problem.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     
  5. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Heidi" <Heidi@discussions.microsoft.com> wrote in message
    news:C6F8998B-C48A-4F4B-A763-4261AD717CE8@microsoft.com
    > This is a report though so I can't get it to pass thru the code (tried
    > watches and breakpoints to check - doesn't even touch it). Doesn't
    > fix the problem..... tried it with the name of the count field....


    I don't follow you. I'm pretty sure that the functions works for
    reports and subreports as well as it does for forms and subforms. See
    if the discussion in this thread from 2001 makes it clearer how to use
    it:


    http://groups.google.com/group/micr...roup:*.*access*&rnum=7&hl=en#a3ca4328cb30dc7b

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  6. Ofer

    Ofer
    Expand Collapse
    Guest

    Try this in the fields where you count the records, if there is no data
    return it will return 0, else it should count the records

    =IIf(HasData, count([Names]),0)


    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Heidi" wrote:

    > This is a report though so I can't get it to pass thru the code (tried
    > watches and breakpoints to check - doesn't even touch it). Doesn't fix the
    > problem..... tried it with the name of the count field....
    >
    >
    >
    > "Dirk Goldgar" wrote:
    >
    > > "Heidi" <Heidi@discussions.microsoft.com> wrote in message
    > > news:C540F095-A240-4DFE-ACFF-B3C9B6271E59@microsoft.com
    > > > I have a subreport that has a count for the number of names in it. I
    > > > reference this count in the main report and add it to the count
    > > > there. My problem is that sometimes the sub doens't have any data -
    > > > i've tried IIf, Nz and cant seem to come up with anything that will
    > > > let me have the value be zero if it's null instead of coming up
    > > > #error. Does anyone know?

    > >
    > > Keri Hardwick's nnz function, posted here ...
    > >
    > > http://www.mvps.org/access/forms/frm0022.htm
    > >
    > > .... should solve your problem.
    > >
    > > --
    > > Dirk Goldgar, MS Access MVP
    > > www.datagnostics.com
    > >
    > > (please reply to the newsgroup)
    > >
    > >
    > >
     
  7. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Ofer" <Ofer@discussions.microsoft.com> wrote in message
    news:FEAD8836-CA3E-4A59-85DF-47505463CA0D@microsoft.com
    > Try this in the fields where you count the records, if there is no
    > data return it will return 0, else it should count the records
    >
    > =IIf(HasData, count([Names]),0)


    Good idea, if the nnz() function can't be made to work for some reason.
    I think Heidi wold have to refer to the property in the controlsource of
    the control on the main report that refers to the "count" text box on
    the subreport. Something along the lines of

    =IIf([SubreportControlName].[Report].[HasData],
    [SubreportControlName].[Report]![txtCountControl],
    0)

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  8. Heidi

    Heidi
    Expand Collapse
    Guest

    That worked great - thank you!!!

    "Ofer" wrote:

    > Try this in the fields where you count the records, if there is no data
    > return it will return 0, else it should count the records
    >
    > =IIf(HasData, count([Names]),0)
    >
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "Heidi" wrote:
    >
    > > This is a report though so I can't get it to pass thru the code (tried
    > > watches and breakpoints to check - doesn't even touch it). Doesn't fix the
    > > problem..... tried it with the name of the count field....
    > >
    > >
    > >
    > > "Dirk Goldgar" wrote:
    > >
    > > > "Heidi" <Heidi@discussions.microsoft.com> wrote in message
    > > > news:C540F095-A240-4DFE-ACFF-B3C9B6271E59@microsoft.com
    > > > > I have a subreport that has a count for the number of names in it. I
    > > > > reference this count in the main report and add it to the count
    > > > > there. My problem is that sometimes the sub doens't have any data -
    > > > > i've tried IIf, Nz and cant seem to come up with anything that will
    > > > > let me have the value be zero if it's null instead of coming up
    > > > > #error. Does anyone know?
    > > >
    > > > Keri Hardwick's nnz function, posted here ...
    > > >
    > > > http://www.mvps.org/access/forms/frm0022.htm
    > > >
    > > > .... should solve your problem.
    > > >
    > > > --
    > > > Dirk Goldgar, MS Access MVP
    > > > www.datagnostics.com
    > > >
    > > > (please reply to the newsgroup)
    > > >
    > > >
    > > >
     
  9. Heidi

    Heidi
    Expand Collapse
    Guest

    Perfect - this completed the question - thanks a bunch!!!

    "Dirk Goldgar" wrote:

    > "Ofer" <Ofer@discussions.microsoft.com> wrote in message
    > news:FEAD8836-CA3E-4A59-85DF-47505463CA0D@microsoft.com
    > > Try this in the fields where you count the records, if there is no
    > > data return it will return 0, else it should count the records
    > >
    > > =IIf(HasData, count([Names]),0)

    >
    > Good idea, if the nnz() function can't be made to work for some reason.
    > I think Heidi wold have to refer to the property in the controlsource of
    > the control on the main report that refers to the "count" text box on
    > the subreport. Something along the lines of
    >
    > =IIf([SubreportControlName].[Report].[HasData],
    > [SubreportControlName].[Report]![txtCountControl],
    > 0)
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     

Share This Page