Concatenating Title and Name Strings in Access

Question

I'm trying to concatenate the title, first name, and last name of a contact into one string in an Access report. Currently I am using a text field with the control set to

=[ContactTitle] & ' ' & [FirstName] & ' ' & [LastName]

However, some contacts don't have a title associated with them and my method leaves a leading space which makes the text alignment on my report look sloppy. So I'm wondering, is there a way to concatenate but only include that first space if the contact title is not null? I am aware of the plus operator but not experienced enough to see a way to use it in this case without just making my entire string null.

Answer 1

You can use the + operator for concatenation.

Concatenating with + yields Null if any of the values you're concatenating are Null:

=([ContactTitle] + " ") & ([FirstName] + " ") & [LastName]

Do note that some devs frown upon using + for concatenation, stating that & is the concatenation operator in VBA.

Also note that if one of the parameters is a zero-length string, this won't work. Only a real Null will lead to the result being Null.