Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Merging text fields

Discussion in 'Information Technology' started by dtfrancis15uk, Jul 28, 2006.

  1. dtfrancis15uk

    dtfrancis15uk
    Expand Collapse
    Guest

    In trying to import an excel spreadsheet to Outlook for contacts, I need to
    merge three address fields into one (They are called Address1, Address2 and
    Address3).

    When doing this in Access by saying (for example) [Address1] + ',' +
    [Address2] I get a blank result field when either of Address1 or Address2 is
    blank. How can I get over this?

    Also, instead of a , between the field values, I would like a carriage
    return - is this possible and how?

    Many thanks
     
  2. Loading...

    Similar Threads Forum Date
    India Punjab emerging as major drug producing centre Breaking News Mar 13, 2013
    Under-19 emerging sikh cricketers Sports & Fitness Jan 22, 2010
    Merging in the Word Of Sabad Sikh Sikhi Sikhism Jan 26, 2008
    Sikh News Punjab emerging as new international education hub (New Kerala) Breaking News Sep 17, 2007
    Sikh News Punjab emerging as new international education hub (ANI via Yahoo! India News) Breaking News Sep 17, 2007

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    You can take advantage of the different behaviour of the '+' and '&'
    operators when concatenating strings. When you use the '+' operator, if any
    part of the expression is Null, the result of the expression is Null. For
    example, in the Immediate Window ...

    ? "one" + null
    Null

    The '&' operator gives a different result. For example, in the Immediate
    Window ...

    ? "one" & null
    one

    You can use this difference both to avoid having the entire expression
    evaluate as Null, and also to avoid a blank line in the middle of your
    address. For example, in the Immediate Window again ...

    ? ("Address1" + Chr$(13) + Chr$(10)) & (Null + Chr$(13) + Chr$(10)) &
    ("Address3" + Chr$(13) + Chr$(10))
    Address1
    Address3

    Note the use of parentheses and '+' and '&' operators, to avoid having the
    whole expression evaluate as Null, and also to avoid a blank line between
    "Address1" and "Address3", despite the presence of the Null value between
    them.

    Chr$(13) and Chr$(10) (in that order) will insert a carriage return and line
    feed to start a new line. In VBA code you can use the intrinsic constant
    vbCrLf instead, but in expressions and queries you need to use the Chr$()
    function.

    --
    Brendan Reynolds
    Access MVP

    "dtfrancis15uk" <dtfrancis15uk@discussions.microsoft.com> wrote in message
    news:3F24E53E-A8C0-4242-8AE5-DD43BE5F8BB5@microsoft.com...
    > In trying to import an excel spreadsheet to Outlook for contacts, I need
    > to
    > merge three address fields into one (They are called Address1, Address2
    > and
    > Address3).
    >
    > When doing this in Access by saying (for example) [Address1] + ',' +
    > [Address2] I get a blank result field when either of Address1 or Address2
    > is
    > blank. How can I get over this?
    >
    > Also, instead of a , between the field values, I would like a carriage
    > return - is this possible and how?
    >
    > Many thanks
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    dtfrancis15uk wrote:
    > When doing this in Access by saying (for example) [Address1] + ',' +
    > [Address2] I get a blank result field when either of Address1 or Address2 is
    > blank. How can I get over this?
    >
    > Also, instead of a , between the field values, I would like a carriage
    > return


    By 'blank result' do you mean NULL or zero-length string? This will
    handle both:

    SELECT IIF(Address1 = '' OR Address1 IS NULL, '', Address1 & CHR$(13))
    & IIF(Address2 = '' OR Address2 IS NULL, '', Address2 & CHR$(13))
    & IIF(Address3 = '' OR Address3 IS NULL, '', Address3 & CHR$(13))
    AS address_concat
    FROM Test

    ....but will return either a concatenated address with a trailing
    carriage return or a zero-length string. To adjust:

    SELECT IIF(LEN(address_concat) = 0, '{{NO_ADDRESS}}',
    LEFT$(DT1.address_concat, LEN(DT1.address_concat) - LEN(CHR$(13)))
    ) AS full_address
    FROM
    (
    SELECT IIF(Address1 = '' OR Address1 IS NULL, '', Address1 & CHR$(13))
    & IIF(Address2 = '' OR Address2 IS NULL, '', Address2 & CHR$(13))
    & IIF(Address3 = '' OR Address3 IS NULL, '', Address3 & CHR$(13))
    AS address_concat
    FROM Test
    ) AS DT1;

    Jamie.

    --
     

Share This Page