Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts
    I have a calculation that takes the sum value from a subform.

    If the subform has no data in it I get #error in the calculation which stuffs up everything else.

    I have tried this

    =nz([sbfQBOrderInfo].[Form]![curBillTot],0)

    but a lack of data due to an empty subform obviously doesn't return a null

    What should I do for the value to return 0 if the subform is empty?
    "Heading for the deep end"

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='weyrman' post='792394' date='07-Sep-2009 16:41']I have a calculation that takes the sum value from a subform.

    If the subform has no data in it I get #error in the calculation which stuffs up everything else.

    I have tried this

    =nz([sbfQBOrderInfo].[Form]![curBillTot],0)

    but a lack of data due to an empty subform obviously doesn't return a null

    What should I do for the value to return 0 if the subform is empty?[/quote]
    Check out this link, especially where it refers to RecordsetClone.RecordCount = 0

    http://bytes.com/topic/access/answers/1938...ubform-mainform

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Does the sum get calculated in the footer of the subform?

    I just did a test and found that it worked if I put the NZ function in the subform sum.

    =NZ(Sum([Amount]),0)
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='792421' date='07-Sep-2009 14:01']Does the sum get calculated in the footer of the subform?

    I just did a test and found that it worked if I put the NZ function in the subform sum.

    =NZ(Sum([Amount]),0)[/quote]

    John,
    Long time - hope things are going well.
    I have the same issue but am trying to sum on a form created from a Cross Tab Query. I tried your nz process but did'nt seem to work . Is this because its a cross tab as the source
    Steve (ex Anna Bay)
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

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