How to Eliminate a Comma in Name Fields, if Field has Only One Name? i.e. Aerosmith, Cher, etc.

[tatihulot]

Hello, I am using Access 2013 and have created a music inventory database.


I have a question about formatting some text in a table. One of my tables has the following fields:

tblArtist
Artist ID
DVD Number
Artist Last Name
Artist First Name
Artist Full Name

In my form, I want the artist's name to appear like this:

Mozart, Wolfgang
Dylan, Bob
Beatles
Aerosmith

I know how to format the table to do this, and I know how to construct the form for this.

To format the name, in the tblArtist table, I use the following expression in the Artist Full Name field:
[Artist Last Name] & ", " & [Artist First Name]

If an artist has only one name, such as Aerosmith, I enter it in the Artist Last Name column. Artists such as The Beatles or The Rolling Stones, I just omit "The."

Here's the problem: When a one-name artist's full name appears in the form, there is a comma after their name. For example:

Aerosmith,
Beatles,

Obviously, I'd want the field to appear like this, with no comma:

Aerosmith
Beatles

My question is this: Is there a simple way to eliminate the comma if there is no text in the Artist First Name column? If so, what is the simplest, easiest, most practical novice-friendly way to do this?
Thank you! TH

[JoeM]

Try:

IIF([Artist First Name],[Artist Last Name] & ", " & [Artist First Name],[Artist Last Name])