Great course and very helpful.  However, I want to build a FullName with a MiddleName, but some people don't have middle names or Initials.  I get two spaces when I add the MiddleName field and it is blank.  Is there a way to "trim" out those spaces?  I thought zero length might work, but alas . . .

---

Reply from Richard Rost:

There's a little trick that I teach in my SQL Seminar that if you use the + sign instead of & it will automatically turn a NULL+" " into just a NULL (because if you add NULL to anything, it's NULL).

For example, if you have:

FN,MI,LN
Joe,A,Smith
Peter,,Watson

If you say:

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

You get:

Joe A Smith
Peter  Watson

Notice the two spaces between Peter and Watson because MiddleName was NULL, but you're still adding spaces around it. Now, if you change the equation to this:

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

This says to ADD the space to MiddleName using STRING ADDITION, not CONCATENATION. It's slightly different in how it's treated, but the benefit is that if you have a NULL value, that equates to NULL. (Null plus anything is Null). Now you get:

Joe A Smith
Peter Watson

This would probably make a good tip video, huh? :)