Joining muliple fields in a report with additional text 1

[Dophia]

Hi
I have a report which joins the address fields from a query and prints in a report.

The control source of a text box is as follows:

=[QryPoundSheet-a.House_No] & " " & [QryPoundSheet-a.Street_Address] & " " & [QryPoundSheet-a.Street_Suffix] & " " & [QryPoundSheet-a.N/S/E/W] & " " & [QryPoundSheet-a.PO_Box] & " " & [QryPoundSheet-a.City] & " " & [QryPoundSheet-a.Province]

Results in Eg "100 First St N 56 Oshawa ON", where 56 is the box number.

The problem is that when there is a PO Box number in the field, the report shows the PO Box number, but the viewer doesn't know what the number refers to. So, I would like to have "PO Box" appear before the number from the field. But if there isn't a PO Box, then I don't want "PO Box" to appear. Is this possible?

Any guideance would be appreciated.
Sophia (Dophia)

 

[jebry]

Hi Sophia!

Try this:

=[QryPoundSheet-a.House_No] & " " & [QryPoundSheet-a.Street_Address] & " " & [QryPoundSheet-a.Street_Suffix] & " " & [QryPoundSheet-a.N/S/E/W] & " " & IIf(IsNull([QryPoundSheet-a.PO_Box]) = True, [QryPoundSheet-a.PO_Box], "POBox " & [QryPoundSheet-a.PO_Box]) & " " & [QryPoundSheet-a.City] & " " & [QryPoundSheet-a.Province]

hth

Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Dophia]

Thank you Jeff! It works perfectly.
Sophia