IIf statement fails

[Robin Chapple]

I have a telephone number field that I need in a report if it is
populated.

I have used this previously:

=IIf(IsNull([PhoneAH])," ",[PhoneAH] & " AH")

With the current project I still get the literal when the field is
empty.

What have I missed?

Thanks,

Robin Chapple

 

[Ken Snell \(MVP\)]

Likely the field is not empty (Null), but has an empty string ("") in it.

Try this:

=IIf(Len([PhoneAH] & "") = 0," ",[PhoneAH] & " AH")

 

[Robin Chapple]

Thanks Ken,

Between us we have won. Your suggestion failed so I removed the zero
and used a "1" which worked.

Then for the [PhoneBH] that did not work but this did:

=IIf(Len([PhoneAH] & "")<4," ",[PhoneAH] & " AH")

I assume that means that I have inherited some very dirty space!

I have now applied that technique to the fax and mobile numbers.

All is well. Thanks again.

Robin

 

[John Spencer]

You could have modified Ken's statement slightly to account for any number of
spaces. That way you wouldn't have to worry about the number of spaces.

=IIF(Len(Trim([PhoneAH] & ""))=0,Null,[PhoneAH] & " AH")

Thanks Ken,

Between us we have won. Your suggestion failed so I removed the zero
and used a "1" which worked.

Then for the [PhoneBH] that did not work but this did:

=IIf(Len([PhoneAH] & "")<4," ",[PhoneAH] & " AH")

I assume that means that I have inherited some very dirty space!

I have now applied that technique to the fax and mobile numbers.

All is well. Thanks again.

Robin

Likely the field is not empty (Null), but has an empty string ("") in it.

Try this:

=IIf(Len([PhoneAH] & "") = 0," ",[PhoneAH] & " AH")