I have fields "Title," "Last Name," "First Name," "Spouse Name." All records have a "Last Name." In various combinations, some records also have a "Title," "First Name," and "Spouse Name." I want to concantinate to produce calculated fields such as: Doe, John Doe, Mrs. John Doe, John and Jane Doe. How can I do that and eliminate unnecesssary spaces?

Hi Cass,

Do you mean something like this:

Last Name &", " & First Name & " " & Title & Case(IsEmpty(Spouse Name), "", " and " & Spouse Name)

HTH

Lee

Hi Lee,

Thanks for anwering my question! Yes, that's exactly what I want. I was doing this in a spread sheet but since the presence of a title and or spouse name is random, I would have to:

First, sort on the Title field (column), then concatinate the records with Title, First Name, and Last Name (Title and Spouse Name are mutually exclusive), then

Second, sort the remainder of the records on the Spouse Name field and concatinate Spouse Name, First Name, Last Name, then

Third, concatinate the remaining records First Name, Last Name.

As you can see, that's several steps (although I got pretty proficient from practice). I expect I could do all this with a script but wow! Somewhere, I saw a method for concatinating without adding redundant spaces but can't remember where. If I concatinate all records the same and have one that doesn't have a Spouse Name, I could wind up with Joe and Doe. I hadn't thought of using CASE but will look into it. Thanks again.

Cass