blank fields on a name line

[vsaun]

I have a report which prints a persons full name on one row/line, using the
following fields/text boxes: Title, First Name, Middle/Maiden Name, Last
Name, Suffix. Most of the names do not have a title, Middle or Suffix data.
How can I get the report to move the info together if one of the fields is
empty. For example -
"John Smith" instead of "[blank title] John [blank middle] Smith [blank
suffix].
I noticed others have asked about omitting blank spaces, but my problem is
that the fields/text boxes are on the same line.

 

[Marshall Barton]

I have a report which prints a persons full name on one row/line, using the
following fields/text boxes: Title, First Name, Middle/Maiden Name, Last
Name, Suffix. Most of the names do not have a title, Middle or Suffix data.
How can I get the report to move the info together if one of the fields is
empty. For example -
"John Smith" instead of "[blank title] John [blank middle] Smith [blank
suffix].
I noticed others have asked about omitting blank spaces, but my problem is
that the fields/text boxes are on the same line.

A common approach to that is to only use one text box. The
text box expression could be something likeL

=(Title + " ") & FirstName & " " & (Middle + " ") & LastName
& (" " + Suffix)

The key to that kind of expression is based on the
difference between the way & and + do concatenation with a
Null value.
S & Null results in S
S + Null results in Null

 

[Armen Stein]

A common approach to that is to only use one text box. The
text box expression could be something likeL

=(Title + " ") & FirstName & " " & (Middle + " ") & LastName
& (" " + Suffix)

The key to that kind of expression is based on the
difference between the way & and + do concatenation with a
Null value.
S & Null results in S
S + Null results in Null

Marshal's approach will work perfectly for you.

If you would like to see a reusable FullName function that uses this
approach, take a look at our free Cool Tools download at
www.JStreetTech.com/downloads. It also has a similar function that
concatenates the fields of an address into one textbox suitable for
printing on an envelope.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com