Extracting a surname

How do I extract a surname from a name field in which the name is in
the form: .

My first though was to run an update query, but I can't find the
function that will give me the position of the comma in the field, so
that I can perform a Left() extraction up to the comma. (ie, I'm
basically looking for something that gievs the same results as the
"search" function in Excel.)

I appreciate any help...

--
Charlie Hoffpauir
http://freepages.genealogy.rootsweb.com/~charlieh/

Re: Extracting a surname

On Oct 22, 3:21 pm, Charlie Hoffpauir wrote:
> How do I extract a surname from a name field in which the name is in
> the form: .
>
> My first though was to run an update query, but I can't find the
> function that will give me the position of the comma in the field, so
> that I can perform a Left() extraction up to the comma. (ie, I'm
> basically looking for something that gievs the same results as the
> "search" function in Excel.)
>
> I appreciate any help...
>
> --
> Charlie Hoffpauirhttp://freepages.genealogy.rootsweb.com/~charlieh/

The InStr function is what you are looking for.
InStr(your_string_here ,",") returns the position of the first comma.

Re: Extracting a surname

On Mon, 22 Oct 2007 13:21:35 -0500, Charlie Hoffpauir wrote:

> How do I extract a surname from a name field in which the name is in
> the form: .
>
> My first though was to run an update query, but I can't find the
> function that will give me the position of the comma in the field, so
> that I can perform a Left() extraction up to the comma. (ie, I'm
> basically looking for something that gievs the same results as the
> "search" function in Excel.)
>
> I appreciate any help...

LastName: Left([FullName],InStr([FullName],",")-1)
FirstName:Mid([FullName],InStr([FullName],",")+2)

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Re: Extracting a surname

On Mon, 22 Oct 2007 12:25:13 -0700, fredg
wrote:

>On Mon, 22 Oct 2007 13:21:35 -0500, Charlie Hoffpauir wrote:
>
>> How do I extract a surname from a name field in which the name is in
>> the form: .
>>
>> My first though was to run an update query, but I can't find the
>> function that will give me the position of the comma in the field, so
>> that I can perform a Left() extraction up to the comma. (ie, I'm
>> basically looking for something that gievs the same results as the
>> "search" function in Excel.)
>>
>> I appreciate any help...
>
>LastName: Left([FullName],InStr([FullName],",")-1)
>FirstName:Mid([FullName],InStr([FullName],",")+2)

Thanks to both of you.

That worked just fine for the database I have in which the names were
formatted .

Now I find additional data in which the names are entered Second Surname (Jr., Sr., II, etc)>

Is there an easy approach like you gave me using InStr (or some other
function) that will work from the right rather than from the left? (I
can eliminate all the Jrs Srs etc by creating a temporary name field
and removing all those trailing suffixes.) What I'd be looking at
then is the location of the "last" space in the name field.

Either that, or a routine that would enable me to just "reverse" all
the characters in the name field.... I could then work from the left
going to the first space, extract the surname, then again reverse the
characters.


--
Charlie Hoffpauir
http://freepages.genealogy.rootsweb.com/~charlieh/

Re: Extracting a surname

On Tue, 23 Oct 2007 20:07:59 -0500, Charlie Hoffpauir

Thanks, but I found it now, using the InStrRev function.


--
Charlie Hoffpauir
http://freepages.genealogy.rootsweb.com/~charlieh/