=?Utf-8?B?S0FSTCBERVdFWQ==?=
wrote in :

There are several ways including an IIF statement.

=[surname] &", " & IIF([middlename] Is Null OR [middlename] = "",
[firstname], [firstname] & " " & [middlename])

But capitalizing on the Null propagation of the + concatenation
operator can make for something much simpler (and more reliable in
producing proper results):

Mid(("12"+LastName) & (", "+(Trim(FirstName & (" "+MiddleName)))),
3)

This handles more combinations of Nulls and non-Nulls than the
formula above.

For what it's worth, I long ago stopped storing middle initial/name
as a separate field, as it's not an independent piece of data. The
only scenario in which it's useful to separate it out is when you
want to construct a salutation field -- "Dear David" is much nicer
than "Dear David W.", of course. But you then gain another problem
because you end up with the ambiguity of where to put data when
someone has more than one candidate middle name/initial.

I instead store a Salutation field so that where the default value
is inappropriate, the user can fill in a proper value. This also
allows for salutations not permanently tied to the name fields, such
as Robert Smith's salutation can be "Dear Bob". To me, structuring
your name storage for the purpose of constructing a saluation field
is a wrong design. A salutation field is sufficiently independent to
get its own data field, and that allows far more flexibility than is
possible with deriving it from the name fields, and also allows the
name storage to be simpler.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/