Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2008
    Posts
    8

    Unanswered: IIF, Null, Nz, or ??

    Hi all

    I have my same old main form with a million subforms. Sometimes the subforms have no data and i am trying to print the rest of the report normally. But I continually get the #error on my totals control.

    So here is how it works - well actually it doesn't work....lol

    On subform 'FaR' I have the following fields which are populated, or not populated, from one of my queries:

    Field1 is a total field from the query
    Field2 is a total field from the query
    Both of these fields are hidden
    Field3 is the sum of Field1+Field2 and shows on the report
    Very often Field3 has no value.

    Then on the Report footer I have Field4 which sums Field3 when it has a value.

    On the main form I have Field5 which is a total for Field3 on this report and another field from the previous subreport. It is this Field which is upsetting my hairdo!!!! Always returning a #error when all the above fields have no values. It all works just fine and dandy so long as there are values but when there are no values, welllllll............???????

    The current calculation I have there is =FaR.Report.FATot+CaR.Report.CATot


    I have tried IIF stuff everywhere I can think of and the same for Nz.

    Can someone please point me towards the correct Field that I need to concentrate on to fix my problem.

    Thanks so much
    Daisy

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have you tried:

    =Nz(FaR.Report.FATot,0)+Nz(CaR.Report.CATot,0)

    You may have to do something similar with "Field3" and/or "Field4".

    Are we talking about forms or reports here?
    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

  3. #3
    Join Date
    Jul 2008
    Posts
    8
    Hello Startrek
    I am really sorry.....it is all reports and subreports
    I have tried your suggestion and when I try to open the report in form view I get the little box asking for the parameter(?) value for say "FaR,0"
    regards
    Daisy

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, try this:

    =IIf(IsNumeric([FaR].[Report].[FATot]),[CaR].[Report].[FATot],0)

    Seems a null test doesn't work when done against missing data.
    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

  5. #5
    Join Date
    Jul 2008
    Posts
    8
    StarTrekker my friend
    You are so helpful.

    I messed with your code so much and got some parts of one subform working and some not working.

    But I did some research on the net using your IIF code and found this:

    =IIf(FaR.Report.HasData,FaR.Report.FATot,0)

    It just works a treat. So similar to yours but maybe I did something wrong when using yours.

    Anyway I am so grateful to you. THANK YOU.

    Daisy

Posting Permissions

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