Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2009
    Posts
    15

    Unanswered: Nulls in a Report: Please Help!

    I'm currently working on a report that has 4 subreports (freshman report query subreport, sophmore report query subreport, junior report query subreport, senior report query subreport) attached to it that each has a value "hours completed" which is added up in a total box in each subreport.

    Under the subreports i have a "total" section which has boxes that take the values in the total boxes from freshman, sophmore, junior, senior subreports and places them in this section using the formula "=[Freshman Report Query subreport].Report.[total box]" which is changed per subreport.

    My problem is adding nulls (preferibly if box has no value= 0) to these statements but when a subreport isn't present (i.e. someone hasn't completed hours for that year) I recieve error messages.

    This is the general layout of what I'm trying to say:

    Freshman Report Query Subreport:
    Hours completed:
    Freshman total: =SUM([Hours completed])

    Sophmore Report Query Subreport:
    Hours completed:
    Sophmore total: =SUM([Hours completed])

    Junior Report Query Subreport
    Hours completed:
    Junior total: =SUM([Hours completed)]

    Senior Report Query Subreport
    Hours completed:
    Senior total: =SUM([Hours completed])

    Total
    Freshman subtotal:=[Freshman Report Query subreport].Report.[total box]
    Sophmore subtotal:=[Sophmore Report Query subreport].Report.[total box]
    Junior Subtotal: =[Junior Report Query subreport].Report.[total box]
    Senior Subtotal:=[Freshman Report Query subreport].Report.[total box]
    Grand total: =([Freshman subtotal])+([Sophmore subtotal])+([Junior subtotal])+([Senior subtotal])

    I've tried using nz values without success

    Please Help

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    Apr 2009
    Posts
    15
    Awesome! thanks for your quick reply, I'll try it tomorrow

  4. #4
    Join Date
    Apr 2009
    Posts
    15
    I've tried it but I'm not exactly sure where to put in the code, I'm really new to VBA and functions so I'm not exactly sure how to implement the code.

    Please help?

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As the link mentions, you'd want that function in a global (standard) module. If you don't already have one, in the VBA editor Insert/Module. Name the module anything BUT nnz, as you can't have a function and module with the same name.
    Paul

  6. #6
    Join Date
    Apr 2009
    Posts
    15
    Do i have to change anything in the code at all? because it doesn't seem to be working.

    This is what i put in a new module:

    Code:
    Function nnz(testvalue As Variant) As Variant
    'Not Numeric return zero
        If Not (IsNumeric(testvalue)) Then
            nnz = 0
        Else
            nnz = testvalue
        End If
    End Function
    Last edited by Xpirmntl; 05-07-09 at 14:51.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No, it should work as is. How are you using it? Where you had:

    =[Freshman Report Query subreport].Report.[total box]

    you should now have:

    =nnz([Freshman Report Query subreport].Report.[total box])
    Paul

  8. #8
    Join Date
    Apr 2009
    Posts
    15
    I think I got it, thanks for all your help

    I just had to individually add "nnz" to each field.

    Thanks for all your help

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem; glad we got it sorted out.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •