INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to build name with middle names suffix

How to build name with middle names suffix

(OP)
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?

RE: How to build name with middle names suffix

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}

RE: How to build name with middle names suffix

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:

CODE

{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

RE: How to build name with middle names suffix

(OP)
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

RE: How to build name with middle names suffix

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close