IF Error Function

  • Thread starter Thread starter Denver
  • Start date Start date
D

Denver

Hi,

=IIf([Done]="NA","",(IIf([Done]<>"NA",([Done])/(Sum([RLApproved])))))

I have this as my COntrol Source to one of my report.
Is there anyway to avoid a result of #Num!, the formula is working
will but it only result to some as #Num! when [RLApproved] = 0.

I want to replace that result 0.00% instead of #Num!. what do I need to add
from my formula?

thanks for any help, I appreciated
 
Hi,

=IIf([Done]="NA","",(IIf([Done]<>"NA",([Done])/(Sum([RLApproved])))))

I have this as my COntrol Source to one of my report.
Is there anyway to avoid a result of #Num!, the formula is working
will but it only result to some as #Num! when [RLApproved] = 0.

I want to replace that result 0.00% instead of #Num!. what do I need to add
from my formula?

thanks for any help, I appreciated
Click to expand...

=IIf([Done]="NA","",(IIf([Done]<>"NA",(NZ([Done], 0)/Sum(NZ
([RLApproved], 0)))))

Regards,
Branislav Mihaljev
Microsoft Access MVP
 
Try something like this:
=IIf(([Report].[HasData] = False) OR (Sum([RLApproved]) = 0)
OR ([Done] = "NA") OR ([Done] Is Null), Null,
Val([Done]) / Sum([RLApproved]))

[Done] must be a Text type field if it can contain NA, so you need to use
Val() to get the value. Val() can't handle Null, so you need to test for
Null as part of the condition. Division by zero causes error, so you need to
test for that condition. If there are no records in the report, the
expression will generate an error, so you have to test for that also.

You may need to sort out the bracketing.
 
Thanks, Allen Browne

Allen Browne said:
Try something like this:
=IIf(([Report].[HasData] = False) OR (Sum([RLApproved]) = 0)
OR ([Done] = "NA") OR ([Done] Is Null), Null,
Val([Done]) / Sum([RLApproved]))

[Done] must be a Text type field if it can contain NA, so you need to use
Val() to get the value. Val() can't handle Null, so you need to test for
Null as part of the condition. Division by zero causes error, so you need to
test for that condition. If there are no records in the report, the
expression will generate an error, so you have to test for that also.

You may need to sort out the bracketing.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Denver said:
Hi,

=IIf([Done]="NA","",(IIf([Done]<>"NA",([Done])/(Sum([RLApproved])))))

I have this as my COntrol Source to one of my report.
Is there anyway to avoid a result of #Num!, the formula is working
will but it only result to some as #Num! when [RLApproved] = 0.

I want to replace that result 0.00% instead of #Num!. what do I need to
add
from my formula?

thanks for any help, I appreciated
Click to expand...
Click to expand...
 
Back
Top