How to build name with middle names suffix

[DK8]

Relatively new to Crystal.

Trying to build statement to combine, Last, First Middle, Suffix. Not all names have a middle or suffix. So if there is a middle name I need a space between first and middle. Then if there is a suffix I need a [comma][space]suffix

So far I'm to:
{NMMAIN.LAST}&", "&{NMMAIN.FIRST}&
if Not isnull({NMMAIN.MIDDLE}) then (" "&{NMMAIN.MIDDLE})else ""&
if isnull({NMMAIN.MIDDLE}) then"" else
if Not isnull({NMMAIN.SUFFIX}) then (", "&{NMMAIN.SUFFIX})else ""

This will return:
Last, First [space][,]suffix (if there is one). So when there is no suffix I still have a [, ] at the end.
If there is no middle name then I have Last, First[space][comma]Suffix

I suspect I'm missing something separating the if statements?

 

[kray4660]

First off, make sure "Default Values for Nulls" has been selected in the Formula Workshop.

You seem to have an extra If that does not seem necessary. I reworked the If statements to so that it looks a little cleaner.

{NMMAIN.LAST}&", "&{NMMAIN.FIRST}&
if isnull({NMMAIN.MIDDLE}) then "" else " "&{NMMAIN.MIDDLE} &
if isnull({NMMAIN.SUFFIX}) "" else ", "&{NMMAIN.SUFFIX}

 

[pmax9999]

I find it helpful in these situations to use brackets and indents to assist with both development at the time, and readability when reviewed later.

It is not absolutely clear what should be returned with the various combination of name components, but this should assist you to refine the result if it isn't exactly what you want:

{NMMAIN.LAST} + ', ' + {NMMAIN.FIRST} +
(
    If      NOT IsNull({NMMAIN.MIDDLE})
    Then    ' ' + {NMMAIN.MIDDLE}
    Else    ''
)   
    +
(
    If      NOT IsNull({NMMAIN.SUFFIX}) 
    Then    ', ' + {NMMAIN.SUFFIX}
    Else    ''
)

If it doesn't give you what you want and you need further assistance, please provide an example of what the above formula returns and exactly how it differs from what you want.

Cheers
Pet

 

[DK8]

Using your formula I get:
Public, John James, (middle name but no suffix); needing Public, John James
Smith, Jane, (No middle name, no suffix); needing Smith, Jane
Jones, John , III (middle name with suffix); needing Jones, John, III

Now when a record has a middle and suffix it returns correctly.

So in the case of Smith there should be no comma. In the case of Jones

 

[pmax9999]

Sorry, but your last post makes no sense to me.

In my testing for sample data of middle name but no suffix (your John Public example above) returns what you say you need, "Public, John James". Are you saying it includes a comma after the middle name? If that is the case could it be that your data includes a comma.

Please review review your last post and explain exactly what ISN'T working as required, what is returned,and how that differs from what you want.

Cheers
Pete