Handling nulls in totalling fields

Topic

New Reply

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?

Reply | Quote

Replies

Murgatroyd,

Try: [noparse]
=IIf(IsNull([Orders]), 0, [Orders]/Sum([Orders])[/noparse]:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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])) )

Reply | Quote

Murgatroyd,

What percentage are you trying to calculate? Please explain in words. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

Murgatroyd,

Any change you could post a pared down DB so we can see what is actually going on? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

I am unable to post a cut-down version of the database. The tables are in a linked back end (SQL server).

Reply | Quote

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).

Reply | Quote

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?

Reply | Quote