How do I concatenate title, firstname, middleinitial, lastname

[Guest]

I have a mailing list with title, first name, middle initial and last name
fields. I want to concatenate these into a single field for mailing
purposes. I want to format to be Mr. and Mrs. John K. Doe. Some records do
not have title, and others do not have middle initial. Variations would be
John K. Doe (empty title field), Mr. and Mrs. John Doe (empty mi field), or
simply John Doe (nothing in either title or mi fields). I'm using Access
2003.

 

[Allen Browne]

Try this in the Control Source of your text box:

=Trim([Title] + " " & [First Name] + " " & [middle initial] + " " &
[last name])

The Trim() removes any spurious spaces.
The combined use of the 2 concatenation operators (+ and &) solves the
multiple spaces issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Ian Craig Armitage]

Hi,

I have a similar thing in my database.. this is a copy/paste of what i use
in the record source of my report...

SELECT tbl_Customers.CustomerID, IIf(tbl_Customers.cstPrefix Is
Null,"",tbl_Customers.cstPrefix) & IIf(tbl_Customers.cstForenames Is
Null,""," " & tbl_Customers.cstForenames) & IIf(tbl_Customers.cstSurname Is
Null,""," " & tbl_Customers.cstSurname) & IIf(tbl_Customers.cstBusinessName
Is Null,""," (" & tbl_Customers.cstBusinessName & ")") FROM tbl_Customers
ORDER BY [cstSurname];

As you can see, i have a business name field too, but you can easily remove
that.

Cheers

Craig

 

[Guest]

Thanks Allen,
Your solution worked perfectly!
Ellen

Try this in the Control Source of your text box:

=Trim([Title] + " " & [First Name] + " " & [middle initial] + " " &
[last name])

The Trim() removes any spurious spaces.
The combined use of the 2 concatenation operators (+ and &) solves the
multiple spaces issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I have a mailing list with title, first name, middle initial and last name
fields. I want to concatenate these into a single field for mailing
purposes. I want to format to be Mr. and Mrs. John K. Doe. Some records
do
not have title, and others do not have middle initial. Variations would
be
John K. Doe (empty title field), Mr. and Mrs. John Doe (empty mi field),
or
simply John Doe (nothing in either title or mi fields). I'm using Access
2003.

 

[Guest]

Thanks Craig for your wonderful help!

I learned in Allen Browne's response that it is not necessary to test for
the null condition. Here's the code I ended up writing which is a bit easier
than yours. Note that I also have a business field which I didn't mention
earlier.

SELECT [tbl 06 Mailing List].ID, Trim([Title]+" " & [FirstName]+" " &
IIf([MI] Is Null,"",[MI]+". ")+"" & [LastName]+" " & [Business]) AS Name1,
[tbl 06 Mailing List].Contact AS Name2, [tbl 06 Mailing List].StreetAddress,
[City] & ", " & [State] & " " & [Zip] AS CityStateZip, [tbl 06 Mailing
List].Phone, [tbl 06 Mailing List].Email
FROM [tbl 06 Mailing List]
WHERE ((([City] & ", " & [State] & " " & [Zip])<>","))
ORDER BY [tbl 06 Mailing List].ID;

Thanks again,
Ellen

Hi,

I have a similar thing in my database.. this is a copy/paste of what i use
in the record source of my report...

SELECT tbl_Customers.CustomerID, IIf(tbl_Customers.cstPrefix Is
Null,"",tbl_Customers.cstPrefix) & IIf(tbl_Customers.cstForenames Is
Null,""," " & tbl_Customers.cstForenames) & IIf(tbl_Customers.cstSurname Is
Null,""," " & tbl_Customers.cstSurname) & IIf(tbl_Customers.cstBusinessName
Is Null,""," (" & tbl_Customers.cstBusinessName & ")") FROM tbl_Customers
ORDER BY [cstSurname];

As you can see, i have a business name field too, but you can easily remove
that.

Cheers

Craig

I have a mailing list with title, first name, middle initial and last name
fields. I want to concatenate these into a single field for mailing
purposes. I want to format to be Mr. and Mrs. John K. Doe. Some records
do
not have title, and others do not have middle initial. Variations would
be
John K. Doe (empty title field), Mr. and Mrs. John Doe (empty mi field),
or
simply John Doe (nothing in either title or mi fields). I'm using Access
2003.