I am really green when it comes to expressions in Access and it has been many ye

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])

Explanation / Answer

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.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.