Adding Additional IIf Statements to an Expression in Access 2010

Question

I am really green when it comes to expressions in Access and it has been many years since I've had to write IIf statements, so I'm wondering if you can help.  I've got an expression that includes the following fields:  Courtesy Title (i.e., Mr., Ms., etc.), First Name, Middle, Last Name, and Suffix (i.e., Jr., Sr., etc.).  I was able to write an expression using an IIf statement indicating that if a record is missing the Courtesy Title, I didn't want the blank space to appear and that the First Name and subsequent fields should appear.  What I'm stuck at is adding another IIf statement for both the Middle and Suffix fields to also follow the same formula; if a record is missing data in those fields, I don't want blank spaces to appear.  And in particular, there should be a comma preceding the Suffix field if there is data in that field.  If no Suffix in a particular record, I don't want the comma to appear.  I'm stuck and I don't know how to proceed from here.  Thanks in advance!

This is what I've got written so far:

=IIf(IsNull([Courtesy Title]),[First Name] & " " & [Middle] & " " & [Last Name] & ", " & [Suffix],[Courtesy Title] & " " & [First Name] & " " & [Middle] & " " & [Last Name] & ", " & [Suffix])

This is my result:

Mr. John Q. Public, Sr.

Ms. Mary J. Doe, (I don't want to show the comma if no Suffix)

Mr. James  Jones, CPA (I don't want extra space if no Middle)

Answer 1

Try:

=IIf(IsNull([Courtesy Title]),"", [Courtesy Title] & " ") & [First Name] & " " & IIF(IsNull([Middle]),"",[Middle] & " ") & [Last Name] & IIF(isNull([Suffix]),"", ", " & [Suffix])

Answer 2

I learned if from Ken Getz maybe decades ago ;-)

Answer 3

You actually don't need to use IIF() at all in this situation. There's a sneaky trick I learned years ago, back on the late lamented Usenet newsgroup: the + and & operators both concatenate strings, but & treats a NULL as a zero length string, whereas + returns NULL if either operand is NULL. So you can use an expression mixing + and & operators with judicious use of parentheses:

([Courtesy Title] + " ") & [First Name] & ([Middle] + " ") & [Last Name] & (", " + Suffix])

The expressions in parentheses will become NULL and just disappear if the field is null.

Edit: hah! Ninja'd by Marshall, who probably taught me this trick in the first place, if we didn't both learn it from John Viescas or Ken Getz.

Answer 4

For a more advanced and shorter expression that takes advantage of the way + works when concatenating strings, try using:

 =([Courtesy Title] + " ") & ([First Name] + " ") & ([Middle] + " ") & [Last Name] & (", " + [Suffix])

Answer 5

Perfect!  Exactly what I'm looking for!  Thanks, Scott!  I know I would have really struggled with this one.