concantenation problem

[ncfd510]

I am combining the fields LastName, FirstName, Suffix,
Prefix into one field from the four. Some names on my
list are followed by Jr., Ph.D., etc. Some are not.
Other names may be preceed by Dr., Prof. Other names may
just be first and last name. The goal is to have the name
present on the report as: Smith, Jr., Dr. John or Smith,
John.

To try to cover all the variables, the following is the
code written for the concatenated field:

=IIf(IsNull([Suffix]) And IsNull([Prefix]),[LastName]
& ", " & [FirstName],[Lastname] & ", " & [Suffix]
& " " & [Prefix] & " " & [FirstName])

The above is working except when the only fields with data
are the LastName and FirstName. I am getting an extra
comma. Ex: Smith, , John

I have tried many variations and can't delete the second
comma. Can someone suggest what I'm missing?

I can also be emailed at ncfd510(notthisemail)
@earthlink.net

Thanks!
Thanks!

 

[Cheryl Fischer]

How about something like this:

= [LastName] & (" "+[Suffix]) & ", " & ([Prefix]+" ") & [FirstName]

hth,