Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Handling nulls in totalling fields

    I have a report that includes a field in the footer that calculates a total for one of the detail fields, and another detail field that calculates a percentage from the total.

    Field in detail line: [Orders]
    Total field in footer: =Sum([Orders])
    Percentage field in detail line: =[Orders]/Sum([Orders])

    This works fine with most query parameters, except when query parameter [Option] = "X", in which case [Orders] works out as null, which causes the total and percentage fields to give an error message: "The expression is typed incorrectly, or it is too complex to be evaluated...."; i.e., null/sum(null) cannot be calculated.

    How can I make the total and percentage fields conditional so that if [Option] = "X" (which makes [Orders] = null) then the total and percentage fields are displayed as blank instead of causing the error message?
    Last edited by Murgatroyd; 2012-12-13 at 01:39.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    Try:
    =IIf(IsNull([Orders]), 0, [Orders]/Sum([Orders])
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. I tried that as below; however, it is still the same; i.e., if [Option] <> "X" (which makes [Orders] <> null) then it works OK, but if [Option] ="X" (which makes [Orders] = null) then it gives the same error message. If I then click on the OK button, sometimes the report is displayed with "#Type!" for the Percentage field.

    Field in detail line: [Orders]
    Total field in footer: =Iif(IsNull([Orders]),0,Sum([Orders]))
    Percentage field in detail line: =Iif(IsNull([Orders]),0,[Orders]/Sum([Orders])) )
    Last edited by Murgatroyd; 2012-12-14 at 03:28.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    What percentage are you trying to calculate? Please explain in words.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    The Total footer field is the sum of the Orders detail fields. The Percentage detail field is the value of the Orders detail field as a percentage of the Total, like this.

    Orders, Percentage
    50, 25%
    40, 20%
    20, 10%
    60, 30%
    30, 15%

    Total orders: 200

    However, if [Option] = "X", then [Order] = null, so the Order detail fields are blank, and so the Total and Percentage fields should be blank in this case also.
    Last edited by Murgatroyd; 2012-12-14 at 19:58.

  6. #6
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Seasons greetings Geek

    Your intended solution is better because it aims to pick up not only the "X" situation, but any other where there is a Null value.

    I think the issue is in the first part of the IIf which should be Nz([Option])=0 Without the test for zero, the result is zero which is False.

    Cronk

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. When you suggested testing for Nz([Option])=0, did you mean Nz([Orders])=0?

    (The issue is that if the [Option] query parameter = "X", then the [Orders] report detail field = null, which causes an error: "The expression is typed incorrectly, or it is too complex to be evaluated....".

    I have tried the following.

    1. Testing whether the detail field [Order] = null, using IsNull:
    - Field in detail line: [Orders]
    - Total field in footer: =Iif(IsNull([Orders]),0,Sum([Orders]))
    - Percentage field in detail line: =Iif(IsNull([Orders]),0,[Orders]/Sum([Orders]))

    2. Testing whether the detail field [Order] = null, using Nz:
    - Field in detail line: [Orders]
    - Total field in footer: =Iif(Nz([Orders])=0,0,Sum([Orders]))
    - Percentage field in detail line: =Iif(Nz([Orders])=0,0,[Orders]/Sum([Orders]))

    3. Testing whether the query parameter [Option] = "X"
    - Field in detail line: [Orders]
    - Total field in footer: =Iif([Option]="X",0,Sum([Orders]))
    - Percentage field in detail line: =Iif([Option]="X",0,[Orders]/Sum([Orders]))

    In all three cases, if the query parameter [Option] is not "X", which makes the detail field [Orders] <> null, then the report works fine, but if the query parameter [Option] is "X", which makes the detail field [Orders] = null, then the error occurs: "The expression is typed incorrectly, or it is too complex to be evaluated....".

    How can I make this so that if [Option] = "X", which makes [Orders] = null, then the total field in the footer and the percentage field in the detail line are displayed as blank instead of causing the error message?

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    Any change you could post a pared down DB so we can see what is actually going on?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    I am unable to post a cut-down version of the database; however, I have found a partial solution by modifying the underlying query so that if the query parameter [Option] = "X" then the detail field [Order] = zero rather than null. Now when the query parameter [Option] = "X" and hence the detail field [Order] = 0, I can make the total and percentage fields blank as follows.

    - Field in detail line: [Orders]
    - Total field in footer: =Iif(Nz([Orders])=0,"",Sum([Orders]))
    - Percentage field in detail line: =Iif(Nz([Orders])=0,"",[Orders]/Sum([Orders]))

    However, now I am unable to make the detail field [Orders] itself display as blank when zero. I have tried the following.

    1. Using a condition in the Control Source property:
    =Iif([Orders]=0,"",[Orders])

    ... but this gives a "Circular reference" error, although I cannot see why.

    2. Using a format mask in the Format property:
    #;-#;"";""

    ... but the field still displays as 0 when zero.

    How can I make the field display as blank when zero?

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    It makes it extremely difficult if people cannot see your database. Just your report, queries and tables with test data in it should be sufficient for a zipped database for people to see.

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    I am unable to post a cut-down version of the database. The tables are in a linked back end (SQL server).

  12. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by Murgatroyd View Post
    I am unable to post a cut-down version of the database. The tables are in a linked back end (SQL server).
    You can import the tables to a new access database, as well as the other relevant objects (queries, report).

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    I have found a way to make the detail field [Orders] display as blank when zero.

    1. Using a condition in the Control Source property:
    =Iif([Orders]=0,"",[Orders])

    This method was giving a "Circular reference" error; however, I found that this was because the field's text box and data source were both named "Orders". I changed the name of the text box to something else, and this method works OK now.

    2. Using a format mask in the Format property:
    #;-#;"";""

    My understanding is that the four-element mask #;-#;"";"" should display values as positive = 123; negative = -123; zero = blank; null = blank; however, in this case, zero values are still displayed as 0 rather than blank. Is this mask incorrect, or is there another way to use a format mask to display values as blank when zero?

Posting Permissions

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