Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining Name Field (A2K)

    I have a database with 3 fields: LastName, Husband, and Wife. In a name and address report I want to print as follows:

    Last Name Husband Wife
    Smith John Jane John & Jane Smith
    Jones Jim Jim Jones
    Blake Sue Sue Blank

    I have tried using the & with IIf statements using IsNull or Not IsNull but don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Name Field (A2K)

    Try this expression in a query:
    <code>
    FamilyName: Mid((" & "+[Husband]) & (" & "+[Wife]) & " " & [LastName],4)
    </code>
    or this in the control source of a text box on a form or report:
    <code>
    =Mid((" & "+[Husband]) & (" & "+[Wife]) & " " & [LastName],4)
    </code>
    I have assumed that there will always be at least one first name. If there are records with a last name without a first name for either husband or wife, the expression will return an incorrect result.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Name Field (A2K)

    Thank so much. It works perfectly. I afraid that I don't understand how it works. Would you please give me a brief explaination?

    Tom

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Name Field (A2K)

    Thanks for the explanation. I was not familiar with the differences between + and &.

    Tom

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Name Field (A2K)

    The expression exploits the difference in behavior between + and & when it comes to Null values: Null & "any string" results in "any string", but Null+"any string" results in Null.

    The expression " & "+[Husband] results in " & Jim" if [Husband] = "Jim" but in Null if [Husband] is Null.
    Similarly, the expression " & "+[Wife] results in " & Anne" if [Wife] = "Anne" but in Null if [Wife] is Null.
    So the concatenation (" & "+[Husband]) & (" & "+[Wife]) results in either " & Jim & Anne" or " & Jim" or " & Anne". In all cases, the string begins with " & ". By taking Mid(..., 4) we remove this initial " & " from the result.

Posting Permissions

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