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.

Jacynth

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.

Busiek

The question says that it's the [ContactTitle] field that may be null, not the other two. – Onset

@Onset True, I'm checking for the other field too to illustrate how to use this concatenation technique. The parentheses are the only added characters and it has no real relevance for execution time. – Busiek

Ah right, sorry I thought you may have just misread the question. – Onset

This is neat, i never knew you could do that, +1 – Anemochore

Answer 2

You could use the IIf() method to check if the title is null and then depending on the answer insert or don't insert a space.

You can also use the Nz() method to check for zero-length strings at the same time as null strings by setting all null values to be a zero-length string and then comparing the result to "".

The code I would recommend you use here is:

=IIf(Nz([ContactTitle],"") = "", "", [ContactTitle] & " ") & [FirstName] & " " & [LastName]

If you you have any problems with this or need a better explaination leave a comment and I'll get back to you.

Onset