Adding Suffix to Last Name if Suffix Exists

[Drew]

I am using Access to build some reports. I am developing an ADP, with a SQL
backend. I need to find out how to add the suffix to a person's last name
if the suffix exists. Like this,

FirstName LastName Suffix
John Smith
Jack London Jr.
Peter Hall Sr.

Now I would need to display this on the report.

John Smith
Jack London, Jr.
Peter Hall, Sr.

I have tried using the IIf function, but it doesn't seem to work. Any
ideas?

Thanks,
Drew

 

[Allen Browne]

Try a text box with ControlSource of:
=[FirstName] + " " & [Surname] & " " + [Suffix]

The example takes advantage of a subtle difference between the 2
concatenation operators:
"A" & Null => "A"
"A" + Null => Null

Make sure the Name of this text box is not the same as the name of a field,
e.g. it cannot be called LastName since Access gets confused if it has the
same name as a field but is bound to an expresion.

 

[Drew]

I get the following error if I use this, =[EmpLName] & ", " & [EmpSuffix],

Column (EmpLName) was used in a CALC expression but is not defined in the
roweset.

Any ideas? Is it because I am using ADP instead of a regular MDB.

Thanks,
Drew

Try a text box with ControlSource of:
=[FirstName] + " " & [Surname] & " " + [Suffix]

The example takes advantage of a subtle difference between the 2
concatenation operators:
"A" & Null => "A"
"A" + Null => Null

Make sure the Name of this text box is not the same as the name of a field,
e.g. it cannot be called LastName since Access gets confused if it has the
same name as a field but is bound to an expresion.

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

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

I am using Access to build some reports. I am developing an ADP, with a
SQL
backend. I need to find out how to add the suffix to a person's last name
if the suffix exists. Like this,

FirstName LastName Suffix
John Smith
Jack London Jr.
Peter Hall Sr.

Now I would need to display this on the report.

John Smith
Jack London, Jr.
Peter Hall, Sr.

I have tried using the IIf function, but it doesn't seem to work. Any
ideas?

 

[Drew]

I get the same message if I use, =[EmpLName] & ", "+[EmpSuffix]

Thanks,
Drew

Try a text box with ControlSource of:
=[FirstName] + " " & [Surname] & " " + [Suffix]

The example takes advantage of a subtle difference between the 2
concatenation operators:
"A" & Null => "A"
"A" + Null => Null

Make sure the Name of this text box is not the same as the name of a field,
e.g. it cannot be called LastName since Access gets confused if it has the
same name as a field but is bound to an expresion.

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

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

I am using Access to build some reports. I am developing an ADP, with a
SQL
backend. I need to find out how to add the suffix to a person's last name
if the suffix exists. Like this,

FirstName LastName Suffix
John Smith
Jack London Jr.
Peter Hall Sr.

Now I would need to display this on the report.

John Smith
Jack London, Jr.
Peter Hall, Sr.

I have tried using the IIf function, but it doesn't seem to work. Any
ideas?

 

[Drew]

That worked perfectly... after I finally read your post thoroughly and
changed my element name to something else.

Thanks,
Drew

Try a text box with ControlSource of:
=[FirstName] + " " & [Surname] & " " + [Suffix]

The example takes advantage of a subtle difference between the 2
concatenation operators:
"A" & Null => "A"
"A" + Null => Null

Make sure the Name of this text box is not the same as the name of a field,
e.g. it cannot be called LastName since Access gets confused if it has the
same name as a field but is bound to an expresion.

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

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

I am using Access to build some reports. I am developing an ADP, with a
SQL
backend. I need to find out how to add the suffix to a person's last name
if the suffix exists. Like this,

FirstName LastName Suffix
John Smith
Jack London Jr.
Peter Hall Sr.

Now I would need to display this on the report.

John Smith
Jack London, Jr.
Peter Hall, Sr.

I have tried using the IIf function, but it doesn't seem to work. Any
ideas?