Combining 4 fields, getting extra spaces!

[Kristyski]

Hi all-

I'm trying to take 4 fields (Prefix, FirstName, MiddleInitial, LastName) and combine them into 1 field "WholeName." Below is my calc:

If(Prefix <> " " and MiddleInitial <> " ", Prefix & " " & FirstName & " " & MiddleInitial & " " & LastName, If(Prefix <> " " and MiddleInitial = " ", Prefix & " " & FirstName & " " & LastName, If(Prefix = " " and MiddleInitial <> " ", FirstName & " " & MiddleInitial & " " & LastName, If(Prefix = " " and MiddleInitial = " ", FirstName & " " & LastName, "Check This Contact Name"))))

It ends up returning a space before the first name if there is no prefix and spaces in the middle if there is no middle initial. What am I doing wrong?

Thanks for any help.

[Lee Smith]

Hi Poster:

Try this calculation,

Case(not IsEmpty(Prefix), Prefix & " ") &

Case(not IsEmpty(FirstName), FirstName & " ") &

Case(not IsEmpty(MiddleInitial), MiddleInitial & " ") &

Case(not IsEmpty(LastName), LastName

)

HTH

Lee

[Kristyski]

That's perfect! Thank you so much! I'm going to have to read up on how to use Case statements.

Thanks again.