I am trying to calculate a total percent of 2 subreports in my main report for a given area. I have a formula that works well if both sureports hold a value.
=Val([DailyManSum].[Report]![EscManPrcnt]+[DailyMechSum].[Report]![EscMechPrcnt])/2
The problem arises When one of the feilds, [DailyMechSum].[Report]![EscMechPrcnt], is empty. There is no value in this field, but it isn't always recognized as Null for some reason. When I use the expression -
=Nz([DailyMechSum].[Report]![OkMechPrcnt],1)
It returns the value "1" as if the field ins Null. But when I use-
=IIf([DailyMechSum].[Report]![OkMechPrcnt]=Null,1,0)
It returns the value "0" as if the field is not Null!! I would like to use the IIf expresion so that if [DailyMechSum].[Report]![EscMechPrcnt] is Null it does not try run the above expression, as it would return an error. And i don't want to replace the Null value with a 0 because then is would screw up the percentage calculation. Thanks for your help.
=Val([DailyManSum].[Report]![EscManPrcnt]+[DailyMechSum].[Report]![EscMechPrcnt])/2
The problem arises When one of the feilds, [DailyMechSum].[Report]![EscMechPrcnt], is empty. There is no value in this field, but it isn't always recognized as Null for some reason. When I use the expression -
=Nz([DailyMechSum].[Report]![OkMechPrcnt],1)
It returns the value "1" as if the field ins Null. But when I use-
=IIf([DailyMechSum].[Report]![OkMechPrcnt]=Null,1,0)
It returns the value "0" as if the field is not Null!! I would like to use the IIf expresion so that if [DailyMechSum].[Report]![EscMechPrcnt] is Null it does not try run the above expression, as it would return an error. And i don't want to replace the Null value with a 0 because then is would screw up the percentage calculation. Thanks for your help.
