listing one field or another but not both

[cjones]

I have an organizational alumni report that I am having troubles with. The information will be printed on a brochure which has limited space. My database has home & work phone numbers. On the report I would like their work phone listed but if they don't have a work phone, I want their home phone listed. I don't want both listed. I set the visible option to "No" for home phone. I don't know much about SQL or visual basic but I have been reading a lot. Here is my vb if statement

' Print Home_Phone only if Work_Phone field is empty
If IsNull(Work_Phone) Then
Home_Phone.Visible
End If

I thought my vb statement would override the visible No setting for home phone if my criteria was met but it doesn't work. Anyone have any suggestions? TIA

 

[AlexMidd]

If this is in a report you could use the IIf function,

e.g.

IIf(isnull([Work_Phone]), [Home_Phone], [Work_Phone])

in the Control Source property.

This shows the [Home_Phone] data in the text box if [Work_Phone] is null, but the [Work_Phone] data otherwise.
Have fun! )

Alex Middleton

 

[AlexMidd]

Sorry, that should have been

=IIf(isnull([Work_Phone]), [Home_Phone], [Work_Phone])

Have fun! )

Alex Middleton

 

[cjones]

Thanks Alex!!

It works but has created another problem which I will post on a new thread.