Share via

How do I concatenate 2 fields on an Access Report?

Anonymous
2011-12-19T19:08:36+00:00

I haven't concatenated fields since the days of DOS, using SqlPlus.  As I've reviewed questions regarding concatenation within Access in the forum, I realize that I am hopelessly out of touch. 

I have Addr1 and Addr2 fields in the Contacts table that I am displaying on an Access Report.  Generally, Addr2 is null, but on the occasions when it's not, I need to display the field.  I'd like to concatenate them to save as much space as possible.  I'd also like to trunc the field so the blank spaces in the fields don't display.

In the "old days" I would have trunced & concatenated and renamed the resulting field as follows:  trunc(addr1||', '||addr2) Address,.... 

Can anyone help me with how I would do this within Access?  You'll have to explain each step, in terms of what view to start in, which tab, etc, etc.  I'm very new to Access.

Thanks so much!!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2011-12-19T19:57:49+00:00

    Hust type the concatenation expressiom in the report's addr text box.  There are many ways to to do what you want in an expression, but I think this will do what you want:

       =[addr1] & (", " + [addr2])

    With text values, the & concatenation operator treats Null values as a zero length string, while + will return Null if either operand is Null.

    If you really us a possibility that you can have leading or trailing spaces in the addr fields (Access generally gets rid of them even if they were entered), then use the Trim function:

       =Trim([addr1]) & (", " + Trim([addr2]))

    10+ people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-12-19T19:58:21+00:00

    Maybe put on separate lines --

    =[Addr1] & IIF(IsNull([Addr2]),"",Chr(13) & Chr(10) & "" & [Addr2])

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2017-08-30T21:33:18+00:00

    Worked like a charm with Access 2013.  Thanks.

    0 comments
  3. ScottGem 68,810 Reputation points Volunteer Moderator
    2011-12-19T19:21:00+00:00

    =[Addr1] & IIF(IsNull([Addr2]),"","" & [Addr2])

    0 comments