Query: Add text to results only if field contains data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mfm - 30 Nov 2005 15:53 GMT

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.

Reply to this Message

Allen Browne - 30 Nov 2005 16:04 GMT

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]

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I want one field in query to return address. The field looks like this in
> query design view.
[quoted text clipped - 8 lines]
>
> Is there any way to do this? Thx so much.

Reply to this Message

mfm - 01 Dec 2005 13:58 GMT

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

> I want one field in query to return address. The field looks like this in
> query design view.
[quoted text clipped - 7 lines]
>
> Is there any way to do this? Thx so much.

Reply to this Message