Concatenating new field makes everthing disappear

The following works in reading from my SQL Server table:

Code:

---

        Dim CMD As New SqlCommand
        CMD.Connection = conn

        CMD.CommandText = "SELECT tblPersons.id, "
        CMD.CommandText = CMD.CommandText & "CASE "
        CMD.CommandText = CMD.CommandText & "WHEN gobyn IS NULL "
        CMD.CommandText = CMD.CommandText & "THEN fn "
        CMD.CommandText = CMD.CommandText & "ELSE gobyn "
        CMD.CommandText = CMD.CommandText & "END "
        CMD.CommandText = CMD.CommandText & "+ ' ' + ln "
        CMD.CommandText = CMD.CommandText & "FROM tblPersons ORDER BY "
        CMD.CommandText = CMD.CommandText & "tblPersons.ln, tblPersons.gobyn, tblPersons.fn, tblPersons.suffix"

---

But when I add a field called suffix, all the records which don't have anything in that field come up blank.

Code:

---

        Dim CMD As New SqlCommand
        CMD.Connection = conn

        CMD.CommandText = "SELECT tblPersons.id, "
        CMD.CommandText = CMD.CommandText & "CASE "
        CMD.CommandText = CMD.CommandText & "WHEN gobyn IS NULL "
        CMD.CommandText = CMD.CommandText & "THEN fn "
        CMD.CommandText = CMD.CommandText & "ELSE gobyn "
        CMD.CommandText = CMD.CommandText & "END "
        CMD.CommandText = CMD.CommandText & "+ ' ' + ln + suffix "  '<--add the field here        CMD.CommandText = CMD.CommandText & "FROM tblPersons ORDER BY "
        CMD.CommandText = CMD.CommandText & "tblPersons.ln, tblPersons.gobyn, tblPersons.fn, tblPersons.suffix"

---

How can I correct this? The whole name,suffix included, does appear correctly when there is a value in suffix. The rest of the records are blank. Take out siffix and it's fine (except no suffix).

It sounds like suffix is not actually blank (ie: a string with no characters), but Null (ie: does not even exist, the value is basically "unknown at this time").

When you append a Null to something, it is logically impossible to deduce what the final value should actually be, so the database system makes the overall result equal to Null.

There are various ways for you to specify what should be done instead, such as a Case similar to what you used for gobyn.

Then I would have to do that for fn and ln, too. Ugh! You don't have to test every field when you concatenate in Access.