Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    62

    Unanswered: Add field from subform to main form when one or either is empty

    Hello everyone!
    I meant reports not forms!!

    I have a mainreport which totals price. I have a subreport on the mainreport with another price total. I would like these to be added together to give me a final figure. This I can do.

    i.e =sum([total]) + reportname.Report!SumOfPrice

    However, sometimes the subreport may be empty (Is null?) and so I get an error in the main report.

    I have tried NZ function and am not sure how to put in the Is Null function. Any suggestions would be greatly appreciated!!
    thanks,
    Sue
    Last edited by Sizzle; 07-03-08 at 04:35.

  2. #2
    Join Date
    Jun 2008
    Posts
    163
    I'm new to access so this is probably wrong. Better wait from a reply from StarTrekker or one of the other access pros.

    Anyway, I'm guessing it might look like this:

    If isNull(formname) = true then
    =([total])
    if isNull(formname) = false then
    =sum([total]) + formname.Report!SumOfPrice
    end if
    end if

  3. #3
    Join Date
    Apr 2004
    Posts
    62
    I've been a wally and said I'm working with subforms when I'm acutally working with a sub report and main report! I'll try your suggestion anyway!

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Wally?

    Quote Originally Posted by Sizzle
    I've been a wally and said I'm working with subforms when I'm acutally working with a sub report and main report! I'll try your suggestion anyway!
    I think the Coffecat's suggestion will probably work for you, maybe with a tweek or two. Let us know.

    You don't need the 'double if' the ELSE takes care of that.
    If isNull(reportname.Report!SumOfPrice) = true then
    =([total])
    ELSE
    =sum([total]) + reportname.Report!SumOfPrice
    end if


    What's a Wally?

    Stu
    --If its free, take it for what its worth!

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You might try an expression such as:

    =Nz(Sum([total]),0) + NZ(reportname.Report!SumOfPrice,0)

    If that doesn't work, I'd be looking at using the NZ function on the two sources so that you eliminate nulls from both areas before adding them together.

    IE in the subreport, you calculate SumOfPrice. However you do that, use the NZ function there to replace nullz with zeroz.

    See how you go
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jun 2008
    Posts
    163
    Quote Originally Posted by stuschmied

    You don't need the 'double if' the ELSE takes care of that.
    If isNull(reportname.Report!SumOfPrice) = true then
    =([total])
    ELSE
    =sum([total]) + reportname.Report!SumOfPrice
    end if
    *moment of enlightenment* ...Ahhhh, so that's how it's done

    Thanks Stu! =D

Posting Permissions

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