Label Help

[rcotero]

Here is the issue:



I have been working on a Microsoft Access database for about two months. I am ready to do a mailer and I need to make labels.

I have no problems populating the data in the label format I need.

On the first line of the label, I need first, middle, and last name.
When I create the template for the label, I choose "first name" and insert a space, "middle name" and a then a space, then "last name." For those in my database who have all three names, this works perfectly.

However, those in my list who do not have a middle name end up with two spaces in-between their first and last name. I don't see it as a problem, but my boss will not allow the labels to sent out with the extra space.

Example 1: Ryan Christopher Otero
<FIRST NAME> <MIDDLE NAME> <LAST NAME>

Which populates as: Ryan Christopher Otero

Example 2: Ryan Otero
<FIRST NAME> <MIDDLE NAME> <LAST NAME>

Which populates as: Ryan Otero


Ideas? Help? Solution?

Thanks again!

[cnstarz]

this isn't a solution, but what about Lastname, Firstname MI? that way the space is on the end and isn't visible.

[Rawb]

Assuming that all three (First, Middle, and Last Names) are being stuck into the same field, you can do the following:

New Method:

=[FirstName] & IIf(Not IsNull([MiddleName]), " " & [MiddleName],"") & " " & [LastName]

This one checks to see if the Field MiddleName is empty and, if it is, leaves it out (along with the extra space).