Results 1 to 6 of 6

Thread: Joining fields

  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Should be easy but this is a real struggle for me tonight. If I want to join First Name + Spouse (if there is one) + Last Name, I used the following in my label report:

    =Trim([FirstName] & " and " & [Spouse] & " " & [LastName])

    The problem arises because some people don't have a spouse, and no matter what I do, I still end up with John and Harrison instead of John Harrison.

    Can you help?

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Try this:

    =[FirstName] & " and " + [Spouse] & " " & [LastName]
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Atlanta, Georgia USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    I would change the query feeding the report adding a calculated field

    Name: IIf([Table1]![Spouse] Is Null,[Table1]![FirstName] & " " & [Table1]![LastName],[Table1]![FirstName] & " and " & [Table1]![Spouse] & " " & [Table1]![LastName])

    and then just use the field Name

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Mark's expression: [FirstName] & " and " + [Spouse] & " " & [LastName] achieves exactly the same result as the longer

    IIf([Table1]![Spouse] Is Null,[Table1]![FirstName] & " " & [Table1]![LastName],[Table1]![FirstName] & " and " & [Table1]![Spouse] & " " & [Table1]![LastName])

    whether you put it in a calculated field in the query, or directly in the report (because of the way the + operator behaves.)

    PS: Name is a reserved word in Access, so it is best to avoid using it as a field name. So if you want to use a calculated field in the query, find another name for it, such as Person or ContactName etc.
    Regards
    John



  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Doesn't Marks expression leave 2 blanks between Firstname and Lastname?

    I think this over comes that problem {note the round brackets enclosing " and " + [Spouse]}:
    [FirstName] & (" and " + [Spouse]) & " " & [LastName]

    I have just tested both and they are both the same, so it doesn't matter if the round brackets are included or not. So Mark's solution is correct.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by patt View Post
    Doesn't Marks expression leave 2 blanks between Firstname and Lastname?

    I think this over comes that problem {note the round brackets enclosing " and " + [Spouse]}:
    [FirstName] & (" and " + [Spouse]) & " " & [LastName]
    My solution will only produce a single space if only a first and last name.

    The parentheses aren't really needed, as the + is evaluated before the &. Sometimes, however, it is easier to read with them in.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •