Concatenate only if there is something to concatenate

[bhrosey via AccessMonster.com]

I am working on a member directory and I want to list the member's name like
this;
CoupleName: [lastname] & ", " & [firstname] & " & " & [Spouse], but not
everyone is married and their names show like this "Doe, John &". How do I
get rid of the "&" when someone doesn't have a spouse? Thanks for the help.

Bill R.
God Bless and Merry Christmas!

 

[Rick Brandt]

I am working on a member directory and I want to list the member's
name like this;
CoupleName: [lastname] & ", " & [firstname] & " & " & [Spouse], but
not everyone is married and their names show like this "Doe, John &".
How do I get rid of the "&" when someone doesn't have a spouse?
Thanks for the help.

Bill R.
God Bless and Merry Christmas!

CoupleName: [lastname] & ", " & [firstname] & (" " + [Spouse])

 

[John Spencer]

CoupleName: [lastname] & ", " & [firstname] & ( " & " + [Spouse])

OR

CoupleName: [lastname] & ", " & [firstname] & IIF Len(Spouse & "" ) >0, " &
" & [Spouse], "")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[bhrosey via AccessMonster.com]

Thank you, that worked perfectly!

I am working on a member directory and I want to list the member's
name like this;

[quoted text clipped - 5 lines]

Bill R.
God Bless and Merry Christmas!

CoupleName: [lastname] & ", " & [firstname] & (" " + [Spouse])

 

[bhrosey via AccessMonster.com]

Thanks for the feedback!!

CoupleName: [lastname] & ", " & [firstname] & ( " & " + [Spouse])

OR

CoupleName: [lastname] & ", " & [firstname] & IIF Len(Spouse & "" ) >0, " &
" & [Spouse], "")

I am working on a member directory and I want to list the member's name
like

[quoted text clipped - 7 lines]

Bill R.
God Bless and Merry Christmas!

 

[David W. Fenton]

CoupleName: [lastname] & ", " & [firstname] & (" " + [Spouse])

The perfect method for concatenating first and last names is:

Mid(("12" + LastName) & (", " + FirstName), 3)

That will give you correct results regardless of whether either is
Null.