VB code in Report On Format

[ipower2]

I have a report which pulls the following fields from a query and populates
into a unbound textbox. The vb is on the ON Format of the detail section.
My main problem is that I do not get all the inforation mainly the phone
numbers. I have up to 4 possible phone numbers, however, not all employees
will have information in all 4. The order of the numbers is home phone,
alternate phone 1, mobilephone, and alternate phone 2. I need to be able
through code print the numbers if there is info, but if there isn't then
ignore the field and move on the next record.

This is my code...

Me.Text25 = [LastName] & ", " & [FirstName] & " #" & [Badge #] & _
Chr(13) & Chr(10) & [Address] & ", " & [City] & " " & [PostalCode] & _
Chr(13) & Chr(10) & Format(IIf(Nz([HomePhone], "") = "", [AltPh1], [HomePhone]
& " "), "(@@@)@@@-@@@@") & _
Format(IIf(Nz([AltPh1], "") = "", [MobilePhone], [AltPh1] & " "), "
(@@@)@@@-@@@@") & _
Chr(13) & Chr(10) & Format(IIf(Nz([AltPh1], "") <> "", [MobilePhone] & "
", [AltPh1]), "(@@@)@@@-@@@@") & _
IIf(Nz([AltPh2], "") <> "", "", [AltPh2])

I think I am so close...but I am unsure...I think a new pair of eyes should
solve the problem...

Any help will be appreciated.

Thanks,

Ivan

 

[Allen Browne]

You may be able to get by without the code if you set the Control Source of
the text box to an expression like this:

=[LastName] &
", " + [FirstName] &
" # + Str([Badge #]) &
Chr(13) & Chr(10) &
Str([HomePhone]) &
", " + Str([AltPh1]) &
", " + Str([MobilePhone]) &
", " + Str([AltPh2])

The expression relies on the fact that there is a subtle difference between
the 2 concatenation operators:
"A" & Null => "A"
"A" + Null => Null
and that + has higher priority than &.

This means:
", " + [FirstName]
is null if firstname is null, so the comma-and-space gets dropped without
needing to use an IIf() expression.

The Str() is needed so Access treats the plus as concatenation (not numeric
addition.) You may not need this if the fields are Text (not Number fields.)