Query: Add text to results only if field contains data

[Guest]

I want one field in query to return address. The field looks like this in
query design view.

Address: [Addresses].[StreetNumber]+' '+[Addresses].[Street]+'
'+[Addresses].[Apt]+' '+[Addresses].[POBox]+' '+[Addresses].[City]+'
'+[Addresses].[State]+' '+[Addresses].[Zip]

My issue is that I would like to print the text "PO BOX" immediately before
the [Addresses].[POBox] ONLY if [Addresses].[POBox] contains data.

Is there any way to do this? Thx so much.

 

[Allen Browne]

There are 2 concatenation operators in VB, & and +, and they work slightly
differently:
"A" & Null yields "A"
"A" + Null yields Null

Try something like this:

Address: [Addresses].[StreetNumber] &
' ' + [Addresses].[Street] &
' ' + [Addresses].[Apt] &
' PO BOX ' + [Addresses].[POBox] &
' ' + [Addresses].[City] &
' ' + [Addresses].[State] &
' ' + [Addresses].[Zip]

 

[Guest]

Allen, I'm not an experienced Access user, but I couldn't get this to work,
still kept getting the text " PO Box" for every record, not just records with
a PO entry. However, your reply got me reading up on NULL values, and I
ended up using
IIf(Nz) to return the text "PO BOX" along with the contents of the PO Box
field for only those records with a PO entry. Thank you for your reply and
for pointing me in the right direction