Show First, Middle AND Last Name

Question

I am using Access 2016, and I am trying to combine more than 2 short text fields, (ex. First Middle AND Last name).  

I am able to do 2 short text fields, like below, but when I try to add a 3rd it does not work.

In the field list, I defined a field name as ContactName, and set Data Type to Calculated.  

The equation I used is [FirstName] + " " + [LastName] ; both are defined fields.  

This works great, but I cannot figure out how to get it to show MiddleName also, it always gives me an issue.  

Any help would be appreciated!

Answer 1

You would do the same thing as with First and Last names:

FirstName & " " & MIddleName & " " & LastName

You may need to use the Nz function if you could have Null values:

FirstName & " " & Nz(MIddleName, "") & " " & LastName

Is this a desktop database, or a web database?

Answer 2

Are you trying to do this in a table or query?   A query or form is where you need to do it.

FullName:  [FirstName] & IFF([MiddleName] Is Null, " ", [MiddleName] & " ") & [LastName]

Answer 3

Nulls propagates in arithmetical expressions, so you need to use the ampersand concatenation operator rather than the plus sign.  However, if the middle name is Null, this would cause two spaces to be inserted between the first and last names.  The solution is to use a combination of both to suppress the redundant space in the event of the middle name being null:

FirstName & (" " + MiddleName) & " " & LastName

Answer 4

I am not sure if this is a desktop or web database, but, the more I think about it, it should be a web database.  First time using Access, so I am still getting familiar with it.

I did try using ampersands and quotes, and I even tried this suggestion, but Access does not recognize it as an operator or function:

This is where I am trying to make this happen, but I am not sure if this is the correct location.  Below is my attempt at it, and the fields that I am trying to combine.

Any suggestions?

Answer 5

I am trying to do this in a table, I do not need to worry about a null value, I will always have all 3 fields.  Look at my reply to Scott McDaniel above to see what I am trying to do.

Thank you!