Results 1 to 6 of 6
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    type error if sub report has null vallue

    I have a report that takes a value from the sub report and adds to it

    Ex:
    [Report1Subtotal]+[Subreport1Subtotal]

    The problem is that if the subreport does not have any value for that client it will give an error

    I have tried using an iif statement like
    iif([Subreport1Subtotal] is null, 0, [Subreport1Subtotal])

    but that doesnt work either.



    Is there a way to make this work or will i have to enter a record in the Subreport with a zero vaue?
    Thaks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Thanks for the advice. I read that page and tried it but wasn't really sure how to implement it (new to programming)

    However, that did help me to refine my search more and I found this


    • Guard against subreports that have no data

    If you see #Error displayed when you reference a subreport that has no data, consider employing the HasDataproperty of the subreport. For example, you could wrap your existing expression within an IIF (immediate if) function that returns 0 when no data exists and the correct value otherwise. Here is an example:
    =IIF([Sub].Report.HasData=True, [Sub].Report!Amt, 0)

    It works perfect for my needs and is easier to implement.
    Thanks for your help!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. You would have used it like:

    =nnz([Sub].Report!Amt)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    ok, thank you for explaining how to use it because I wasn't sure how I would have used it.

    So the code would go in a module.
    Then in the MAIN report I would put the nzz in front of the reference to the field that is in the SUB REPORT

    Thanks

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, it's used much like the built-in Nz() function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-24-2013, 02:01 PM
  2. IF ID Is not Null show TYPE from other table
    By superjeff in forum Access
    Replies: 5
    Last Post: 09-14-2012, 09:45 PM
  3. Error: Run-time error '13' Type mismatch
    By uronmapu in forum Access
    Replies: 1
    Last Post: 09-07-2012, 05:38 AM
  4. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  5. Error 13 Type Mismatch error
    By GlennBurg in forum Programming
    Replies: 1
    Last Post: 06-21-2011, 03:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums