I have a NAME field with a person's full name. I have also created a first 
name, middle name and last name field and use the LEFTWORDS(name,1), 
MIDDLEWORDS(name,2,1),  and RIGHTWORDS(name,1),  functions to split up the 
full name.

My problem is if a name doesn't have a middle initial or name. How do I get 
a blank if there isn't a middle initial or name so that it doesn't repeat 
the last name?

I am splitting up the name so that my database can be sorted on last name.

Thanks in advance. 

There are many ways to do this.
A funny one could be :
YrMiddleName (calc) = Case(MIDDLEWORDS(name,2,1) = RIGHTWORDS(name,1),  "", 
MIDDLEWORDS(name,2,1))
Remi-Noel


"MuniciNET" <dan@mmsit.net> a �crit dans le message de news: 
M6GdnbodL60k8aPZnZ2dnUVZ_vqdnZ2d@lbdsl.com...
>I have a NAME field with a person's full name. I have also created a first 
>name, middle name and last name field and use the LEFTWORDS(name,1), 
>MIDDLEWORDS(name,2,1),  and RIGHTWORDS(name,1),  functions to split up the 
>full name.
>
> My problem is if a name doesn't have a middle initial or name. How do I 
> get a blank if there isn't a middle initial or name so that it doesn't 
> repeat the last name?
>
> I am splitting up the name so that my database can be sorted on last name.
>
> Thanks in advance.
> 

I just thought of something else, how do I handle a company?

Would it make more sense to enter names as seperately then join or combine 
them? Then again, what would I do with company names?


"MuniciNET" <dan@mmsit.net> wrote in message 
news:M6GdnbodL60k8aPZnZ2dnUVZ_vqdnZ2d@lbdsl.com...
>I have a NAME field with a person's full name. I have also created a first 
>name, middle name and last name field and use the LEFTWORDS(name,1), 
>MIDDLEWORDS(name,2,1),  and RIGHTWORDS(name,1),  functions to split up the 
>full name.
>
> My problem is if a name doesn't have a middle initial or name. How do I 
> get a blank if there isn't a middle initial or name so that it doesn't 
> repeat the last name?
>
> I am splitting up the name so that my database can be sorted on last name.
>
> Thanks in advance.
> 

Now you know why it's better to build a full name from the parts then to try 
to get the parts from a full name.

The easiest way to solve your problem is to assume:

1) There are always at least two words in a name.
2) The first word is always the first name.
3) The last word is always the last name
4) If there are only two words, there is no middle name
5) Otherwise, everything but the first and last words are the middle name

These are not the best assumptions because sometimes a first or last name 
can have more than one word! ("Words" being defined by spaces.) And what 
would you do about "Prince" and "Madonna?"

Nevertheless... given the above, your LeftWords() and RightWords() functions 
are fine... What needs spiffing up is what you are using for the middle. 
You'll need to use an If() statement. In English:

If there are only two words in the name then forget it, otherwise show me 
everything but the first and last words.

If (test, result1, result 2)

is the format of the If() statement.

If ( WordCount(name) >2 ,
  MiddleWords ( name, 2, WordCount(name) - 2),
  "" )

Is the full If() statement.

- Bill


"MuniciNET" <dan@mmsit.net> wrote in message 
news:M6GdnbodL60k8aPZnZ2dnUVZ_vqdnZ2d@lbdsl.com...
>I have a NAME field with a person's full name. I have also created a first 
>name, middle name and last name field and use the LEFTWORDS(name,1), 
>MIDDLEWORDS(name,2,1),  and RIGHTWORDS(name,1),  functions to split up the 
>full name.
>
> My problem is if a name doesn't have a middle initial or name. How do I 
> get a blank if there isn't a middle initial or name so that it doesn't 
> repeat the last name?
>
> I am splitting up the name so that my database can be sorted on last name.
>
> Thanks in advance.
> 

Company names, now? How are you going to tell the difference between

Marriott Corp

and

Bill Marriott

?

If you're starting from scratch the very best thing you could do is to store 
everything in discreet fields:

FirstName
LastName
MiddleName
Company
Address1
Address2
City
State
Zip

You can easily combine these later on... much more easily than trying to 
pick apart everything lumped into a single field.

Bill

"MuniciNET" <dan@mmsit.net> wrote in message 
news:rLidnUlqIPxw7KPZRVn-ug@lbdsl.com...
>I just thought of something else, how do I handle a company?
>
> Would it make more sense to enter names as seperately then join or combine 
> them? Then again, what would I do with company names?
>
>
> "MuniciNET" <dan@mmsit.net> wrote in message 
> news:M6GdnbodL60k8aPZnZ2dnUVZ_vqdnZ2d@lbdsl.com...
>>I have a NAME field with a person's full name. I have also created a first 
>>name, middle name and last name field and use the LEFTWORDS(name,1), 
>>MIDDLEWORDS(name,2,1),  and RIGHTWORDS(name,1),  functions to split up the 
>>full name.
>>
>> My problem is if a name doesn't have a middle initial or name. How do I 
>> get a blank if there isn't a middle initial or name so that it doesn't 
>> repeat the last name?
>>
>> I am splitting up the name so that my database can be sorted on last 
>> name.
>>
>> Thanks in advance.
>>
>
> 

That's what I thought afterI posted my question. Thanks for your responses 
though, Bill.

Dan

"Bill Marriott" <wjm@wjm.org> wrote in message 
news:I5KdncDvMeg3HKPZnZ2dneKdnZydnZ2d@comcast.com...
> Company names, now? How are you going to tell the difference between
>
> Marriott Corp
>
> and
>
> Bill Marriott
>
> ?
>
> If you're starting from scratch the very best thing you could do is to 
> store everything in discreet fields:
>
> FirstName
> LastName
> MiddleName
> Company
> Address1
> Address2
> City
> State
> Zip
>
> You can easily combine these later on... much more easily than trying to 
> pick apart everything lumped into a single field.
>
> Bill
>
> "MuniciNET" <dan@mmsit.net> wrote in message 
> news:rLidnUlqIPxw7KPZRVn-ug@lbdsl.com...
>>I just thought of something else, how do I handle a company?
>>
>> Would it make more sense to enter names as seperately then join or 
>> combine them? Then again, what would I do with company names?
>>
>>
>> "MuniciNET" <dan@mmsit.net> wrote in message 
>> news:M6GdnbodL60k8aPZnZ2dnUVZ_vqdnZ2d@lbdsl.com...
>>>I have a NAME field with a person's full name. I have also created a 
>>>first name, middle name and last name field and use the 
>>>LEFTWORDS(name,1), MIDDLEWORDS(name,2,1),  and RIGHTWORDS(name,1), 
>>>functions to split up the full name.
>>>
>>> My problem is if a name doesn't have a middle initial or name. How do I 
>>> get a blank if there isn't a middle initial or name so that it doesn't 
>>> repeat the last name?
>>>
>>> I am splitting up the name so that my database can be sorted on last 
>>> name.
>>>
>>> Thanks in advance.
>>>
>>
>>
>
> 

In article <3JadnZ9Pf4bt6KPZRVn-ig@comcast.com>, "Bill Marriott"
<wjm@wjm.org> wrote:

> Now you know why it's better to build a full name from the parts then to try 
> to get the parts from a full name.
> 
> The easiest way to solve your problem is to assume:
> 
> 1) There are always at least two words in a name.
> 2) The first word is always the first name.
> 3) The last word is always the last name
> 4) If there are only two words, there is no middle name
> 5) Otherwise, everything but the first and last words are the middle name
> 
> These are not the best assumptions because sometimes a first or last name 
> can have more than one word! ("Words" being defined by spaces.) And what 
> would you do about "Prince" and "Madonna?"

Of course there are other problems here too:

   - some names are backwards (eg. Asian names) with the family
     name being first and the person's name being last,

   - hyphenated names or double-barrelled names without a hyphen

etc.

As someone else suggested, it's usually better to have the names
entered as separate fields, then you can combine them in anyway needed
via Calculation fields or Merge fields. even then the person entering
the data has to make a guess in some cases ... especially since people
rarely fill out forms correctly (often putting their name backwards) or
readably.  :o(

You may also need a Title field into which you can enter Mr, Mrs, etc.
so that letters without a first name can be addresses to "Mr Jones" or
"Mrs Smith".





Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Ad a suffix field for Jr., III, Esq.

The permutations go on and on :)


"Helpful Harry" <helpful_harry@nom.de.plume.com> wrote in message 
news:140420060845598649%helpful_harry@nom.de.plume.com...
> You may also need a Title field into which you can enter Mr, Mrs, etc.
> so that letters without a first name can be addresses to "Mr Jones" or
> "Mrs Smith".

In article <fIedncZmfK-MIqPZRVn-uQ@comcast.com>, "Bill Marriott"
<wjm@wjm.org> wrote:
> "Helpful Harry" <helpful_harry@nom.de.plume.com> wrote in message 
> news:140420060845598649%helpful_harry@nom.de.plume.com...
> >
> > You may also need a Title field into which you can enter Mr, Mrs, etc.
> > so that letters without a first name can be addresses to "Mr Jones" or
> > "Mrs Smith".
> 
> Add a suffix field for Jr., III, Esq.
> 
> The permutations go on and on :)

True. You could then add a field for Maiden name so letters get
addressed to:

      Mrs Mary Anderson nee Franklin

or how about extending it further to:

      Mrs Mary Anderson nee Franklin
         daughter of Jenny Franklin nee Wellington
            daughter of ...

;o)


Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)