Extract last word in field

[Jana]

Hello,

I'm struggling with trying to extract the last word in a field, so that I can then extract the first initial of that last word.

Someone else set up a name field that contains a persons full name rather than separate FirstName, MiddleName, LastName fields. (Some entries have 2 words some have 3). Example:

Mary Eileen Jones

Jane Smithson

In the end I want to get the initials of the person's first and last names. Thus, MJ or JS from the above example.

I had hoped Last function would work but it doesn't. It gave me all three words in Mary Eileen Jones.

I've managed to extract all kinds of thing with different, but not the last word's first initial.

Please help

thanks

Jana

[sbg2]

Left(RightWords(MyTable::NameField;1);1)

However, If a person has a last name like Von Buron this obviously would not work.

[AudioFreak]

sbg2 that only grabs the 1st letter of the First name.

Left ( LeftWords ( Name Field ;1 ) ; 1) & Left ( RightWords ( Name Field ;1 ) ; 1)

I believe it what Jana is after.

[sbg2]

It grabs the first letter of the LAST WORD. I figured the original poster could figure the rest out.

Edited May 10, 2007 by Guest

[AudioFreak]

Ahh my bad you are correct.....Eating lunch here so I missed that.....lol

[sbg2]

I think it's the "Left(Right" part of the calculation that makes it easy to misinterpret.

If the Name fields are consistent enetered then using Left(MyTable::Name; 1) would work for the first initial. Of course I can see other potential problems like a user typing in a Name as "Smith, John A." or "Mr. John A. Smith".

[AudioFreak]

Yeah I agree...users can screw things up that way quite easily. I also got away from using one field for the name and now only use First_Name and Last_Name now.