Handling Null In Concatenation

[Juan4412]

Let's say I have 3 fields, FirstName, MiddleName, LastName and I want to concatenate them into one

FullName: [FirstName] & " " & [MiddleName] & " " & [LastName]

Well what if middle name was blank? I wouldn't want my output to be: FirstName LastName, that would be odd, how can I handle Null values in concatenation of strings like such?

[ssanfu]

See http://www.spearsofttech.com/article...on-with-nulls/

and

http://windowssecrets.com/forums/sho...-concatenation

and

http://www.fmsinc.com/free/NewTips/A...ccesstip49.asp

These show the difference results using "&" or "+" in concatenating fields.

[June7]

Try:

FullName: [FirstName] & " " + [MiddleName] & " " & [LastName]

[JeffChr]

If you are doing this in a query then it will work as written, except you will have two spaces between the names if the middle name is written. You can correct that: FullName: Replace([FirstName] & " " & [MiddleName] & " " & [LastName]," "," ")

[Juan4412]

Try:

FullName: [FirstName] & " " + [MiddleName] & " " & [LastName]

So what is the difference between the & and + sign between the concatenation?

[June7]

Test it in the VBA immediate window by typing:

?"A" & " " & Null & " " & "B"

?"A" & " " + Null & " " & "B"

The first will return 2 spaces between.

The second will not.

+ sign has two uses: addition and concatenation. The concatenation use is holdover from ancient BASIC.

Anyway, arithmetic with Null returns null. So the " " + Null returns Null instead of the space.

Then Null is just ignored with the & concatenation.