Concatenation Not Working

Why is no 'Actual_Name' given when no 'Middle_Name' is provided in a select query ? Below is the syntax of the statement.

Actual_Name: [First_Name]+" "+[Middle_Name]+" "+[Last_Name]

:banghead:

What is your complete SQL statement?
What is your actual results?
What is your expected results?

Try this instead:


Notice I've replaced + separator with &
Also modified the handling of spaces around the middle name so you don't get two spaces if no middle name exists

Because it propagate the null when you use +, instead:

Actual_Name: ([First_Name] + " ") & ( [Middle_Name]) + " ") & [Last_Name]

The & is the standard VBA concatenation operator and Access also uses it in queries. The + operator does double duty as both an arithmetic and a concatenation operator but it works differently from the & in how it handles nulls. You can use this difference to your advantage when you are concatenating multiple strings such as First middle and last names. If you simply use:

First & " " & Middle & " " & Last

You will end up with two spaces between first and last whenever a person has no middle name. Using the expression shown by arnel which uses both + and & you can avoid introducing the extra spaces. Examine this statement closely to understand how it works. This is much more complicated to accomplish in other languages where + is the only option. You have to use a much more complicated statement with the IIf() to avoid the extra spaces.

BigHappyDaddy/Ridders/Arnelgp/Pat Hartman,

Thanks to all ! ! ! I used Arnelgp's solution.