Microsoft Access

Microsoft Access Joining Names with Expressions

sunshinenerd

Join smallIn this post I will show you how to join customer names (customer plus spouse) and customer salutations. This is very useful when doing Microsoft Word merges into form letters.

Open up the Query that you want to create a field for joining customer names and salutations.

You will need the customers first name, last name, salutation, spouses first name and last name (if there is a spouse).

  1. You will need to insert a new column in the query. To do this, hover over the top of the column where you want to insert the new column. When your cursor turns into a down-arrow, click and it’ll highlight the column. In the top menu, under Query Tools, “Design” tab, click on the “Insert Column” button.

2.  In the “field” row in the new column, paste the following code that has the format you want. Change the names in the brackets to match your field names. My field name for the clients’ last name is [ClientLastName]. Yours might be just [LastName].

JoinName: this code will produce this:
“Smith, John and Sara”
or “John Smith” (if no spouse)
or if spouse has different last name it would look like this: “Smith, John and Jones, Sara”

JoinName: IIf([SpouseLastName]=[ClientLastName],[ClientLastName] & “, ” & [ClientFirstName] & ” and ” & [SpouseFirstName],IIf([SpouseLastName] Is Not Null,[ClientLastName] & “, ” & [ClientFirstName] & ” and ” & [SpouseLastName] & “, ” & [SpouseFirstName],[ClientLastName] & “, ” & [ClientFirstName]))

JoinName: this code will produce this:
“John and Sara Smith”
or “John Smith” (if no spouse)
or if spouse has different last name, it would look like this: “John Smith and Sara Jones”

JoinName: IIf([SpouseLastName]=[ClientLastName],[ClientFirstName] & ” and ” & [SpouseFirstName] & ” ” & [ClientLastName],IIf([SpouseLastName] Is Not Null,[ClientFirstName] & ” ” & [ClientLastName] & ” and ” & [SpouseFirstName] & ” ” & [SpouseLastName],[ClientFirstName] & ” ” & [ClientLastName]))

Salutation: this code will produce this:  “John and Sara”

JoinSal: IIf([SpouseSalutation] Is Not Null,[ClientSalutation] & ” and ” & [SpouseSalutation] & “:”,IIf([SpouseSalutation] Is Null,[ClientSalutation] & “:”))

Note: the words “JoinName” and “JoinSal” is the name of the expression. You can choose any name you want to call it.

By Rana Kory

Leave a comment