Remove "#Error" from a Report

[Guest]

This is a second post, because I think the first was lost because I was
forced to log in again. If not, my apologies.

I have a telephone list on which #Error appears whenever a the telephone
field is blank. The control name for the number is txtPhone2, which draws its
data from another calculated control, txtPhone. The purpose of the latter is
to format the telephone number (xxx) xxx-xxxx, if the parentheses were not
hard-coded when the number was entered.

Here is the calculation for txtPhone2, which I had hoped would eliminate
#Error, but does not: =IIf([txtPhone]=Null, Null, [txtPhone]).
Here is the formatting calculation:
=IIf(Left$([Phone],1)="(",[Phone],Format([Phone],"(@@@) @@@-@@@@")).

Would appreciate any help that can be given to address this issue.

 

[fredg]

This is a second post, because I think the first was lost because I was
forced to log in again. If not, my apologies.

I have a telephone list on which #Error appears whenever a the telephone
field is blank. The control name for the number is txtPhone2, which draws its
data from another calculated control, txtPhone. The purpose of the latter is
to format the telephone number (xxx) xxx-xxxx, if the parentheses were not
hard-coded when the number was entered.

Here is the calculation for txtPhone2, which I had hoped would eliminate
#Error, but does not: =IIf([txtPhone]=Null, Null, [txtPhone]).
Here is the formatting calculation:
=IIf(Left$([Phone],1)="(",[Phone],Format([Phone],"(@@@) @@@-@@@@")).

Would appreciate any help that can be given to address this issue.

Use IsNull() not = Null.

=IIf(IsNull([Phone]),"",[txtPhone])

= IIf(IsNull([Phone]),"",IIf(Left$([Phone],1) =
"(",[Phone],Format([Phone],"(@@@) @@@-@@@@")))

 

[Guest]

The IsNull in the formatting statement did the trick. Thank you So Much!
--
susan

This is a second post, because I think the first was lost because I was
forced to log in again. If not, my apologies.

I have a telephone list on which #Error appears whenever a the telephone
field is blank. The control name for the number is txtPhone2, which draws its
data from another calculated control, txtPhone. The purpose of the latter is
to format the telephone number (xxx) xxx-xxxx, if the parentheses were not
hard-coded when the number was entered.

Here is the calculation for txtPhone2, which I had hoped would eliminate
#Error, but does not: =IIf([txtPhone]=Null, Null, [txtPhone]).
Here is the formatting calculation:
=IIf(Left$([Phone],1)="(",[Phone],Format([Phone],"(@@@) @@@-@@@@")).

Would appreciate any help that can be given to address this issue.

Use IsNull() not = Null.

=IIf(IsNull([Phone]),"",[txtPhone])

= IIf(IsNull([Phone]),"",IIf(Left$([Phone],1) =
"(",[Phone],Format([Phone],"(@@@) @@@-@@@@")))