I have a field with names in it and I would like to get the first and last
names, but the data looks like this.

Jim Bob Brown
Jim Billy Bob Brown
Jim B Brown

If I can assume that the first name is Jim and the last name is brown I
could live with that.
I don't really need the middle part(s).
The names are separated by space.
I'm using Access 2003.

Any great ideas on how to parse this date into first and last names?

Thanks

Kelvin

But you can't assume the last part is the last name or the first part is the
first name.
Jim Billy Bob Brown, Jr.
Jim Billy Bob Brown MD
Jim Billy Bob Brown III
Jim Billy Bob Brown Esq.
Jim Billy Bob Brown, Phd.
Dr. Jim Billy Bob Brown
Sir Jim Billy Bob Brown
Hon Jim Billy Bob Brown

The difficulty of parsing names is exceeded only by the difficulty of
parsing addresses. In a perfect world, each part is carried in a different
field and concantenated, where appropriate, for display purposes. In lieu of
a perfect world, you will need to write a function to evaluate the parts and
determine if it is a name prefix or suffix and omit those that are.
I would start with the Split function.

Dim aryParts as Variant

aryNameParts = Split(strFullName, Space(1))

Will return an array of the parts. Then you can loop through them and pars
--
Dave Hargis, Microsoft Access MVP


"Kelvin Beaton" wrote:

> I have a field with names in it and I would like to get the first and last
> names, but the data looks like this.
>
> Jim Bob Brown
> Jim Billy Bob Brown
> Jim B Brown
>
> If I can assume that the first name is Jim and the last name is brown I
> could live with that.
> I don't really need the middle part(s).
> The names are separated by space.
> I'm using Access 2003.
>
> Any great ideas on how to parse this date into first and last names?
>
> Thanks
>
> Kelvin
>
>
>
>

On Thu, 12 Jul 2007 10:41:45 -0500, "Kelvin Beaton" <kelvin at mccsa dot com>
wrote:

>I have a field with names in it and I would like to get the first and last
>names, but the data looks like this.
>
>Jim Bob Brown
>Jim Billy Bob Brown
>Jim B Brown
>
>If I can assume that the first name is Jim and the last name is brown I
>could live with that.

But you can't.

My friend Darla Sue Jones (well, that's not her real last name) uses Darla Sue
as her first name. It's not Darla, it's Darla Sue.

My former coworker Felix de la Iglesia's last name is de la Iglesia. It's not
Iglesia, and he would quite rightly object if addressed in that way.

Parsing names requires a USB interface - Using Someone's Brain!

John W. Vinson [MVP]

You can use Instr to find the first space and InStrRev to find the last
space. And with that information you should be able to get the First word
and the Last word

LEFT(SomeField, Instr(1,SomeField," ")-1)
RIGHT(SomeField,InStrRev(SomeField," ") +1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Klatuu" <(E-Mail Removed)> wrote in message
news:45C6BEA7-3C8E-4AC8-9378-(E-Mail Removed)...
> But you can't assume the last part is the last name or the first part is
> the
> first name.
> Jim Billy Bob Brown, Jr.
> Jim Billy Bob Brown MD
> Jim Billy Bob Brown III
> Jim Billy Bob Brown Esq.
> Jim Billy Bob Brown, Phd.
> Dr. Jim Billy Bob Brown
> Sir Jim Billy Bob Brown
> Hon Jim Billy Bob Brown
>
> The difficulty of parsing names is exceeded only by the difficulty of
> parsing addresses. In a perfect world, each part is carried in a
> different
> field and concantenated, where appropriate, for display purposes. In lieu
> of
> a perfect world, you will need to write a function to evaluate the parts
> and
> determine if it is a name prefix or suffix and omit those that are.
> I would start with the Split function.
>
> Dim aryParts as Variant
>
> aryNameParts = Split(strFullName, Space(1))
>
> Will return an array of the parts. Then you can loop through them and
> pars
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Kelvin Beaton" wrote:
>
>> I have a field with names in it and I would like to get the first and
>> last
>> names, but the data looks like this.
>>
>> Jim Bob Brown
>> Jim Billy Bob Brown
>> Jim B Brown
>>
>> If I can assume that the first name is Jim and the last name is brown I
>> could live with that.
>> I don't really need the middle part(s).
>> The names are separated by space.
>> I'm using Access 2003.
>>
>> Any great ideas on how to parse this date into first and last names?
>>
>> Thanks
>>
>> Kelvin
>>
>>
>>
>>

That explains a lot.
My USB only operates at 1200 baud with a 64K memory chip. And, the memory
is volitle. Every time I unplug it, it looses its data.
I have been told it is too old to upgrade, I need a replacement.
--
Dave Hargis, Microsoft Access MVP


"John W. Vinson" wrote:

> On Thu, 12 Jul 2007 10:41:45 -0500, "Kelvin Beaton" <kelvin at mccsa dot com>
> wrote:
>
> >I have a field with names in it and I would like to get the first and last
> >names, but the data looks like this.
> >
> >Jim Bob Brown
> >Jim Billy Bob Brown
> >Jim B Brown
> >
> >If I can assume that the first name is Jim and the last name is brown I
> >could live with that.
>
> But you can't.
>
> My friend Darla Sue Jones (well, that's not her real last name) uses Darla Sue
> as her first name. It's not Darla, it's Darla Sue.
>
> My former coworker Felix de la Iglesia's last name is de la Iglesia. It's not
> Iglesia, and he would quite rightly object if addressed in that way.
>
> Parsing names requires a USB interface - Using Someone's Brain!
>
> John W. Vinson [MVP]
>

I'm getting the idea that USB will be required.... ;-)

Thanks

Kelvin


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Thu, 12 Jul 2007 10:41:45 -0500, "Kelvin Beaton" <kelvin at mccsa dot
> com>
> wrote:
>
>>I have a field with names in it and I would like to get the first and last
>>names, but the data looks like this.
>>
>>Jim Bob Brown
>>Jim Billy Bob Brown
>>Jim B Brown
>>
>>If I can assume that the first name is Jim and the last name is brown I
>>could live with that.
>
> But you can't.
>
> My friend Darla Sue Jones (well, that's not her real last name) uses Darla
> Sue
> as her first name. It's not Darla, it's Darla Sue.
>
> My former coworker Felix de la Iglesia's last name is de la Iglesia. It's
> not
> Iglesia, and he would quite rightly object if addressed in that way.
>
> Parsing names requires a USB interface - Using Someone's Brain!
>
> John W. Vinson [MVP]

On Thu, 12 Jul 2007 10:32:03 -0700, Klatuu <(E-Mail Removed)>
wrote:

>That explains a lot.
>My USB only operates at 1200 baud with a 64K memory chip. And, the memory
>is volitle. Every time I unplug it, it looses its data.
>I have been told it is too old to upgrade, I need a replacement.

<SNORK!!!>

Well, the good news is that the add-in chip should be out soon.

The bad news is that it runs on unpatched Windows Vista...

John W. Vinson [MVP]

This can reduce but not eliminate the amount of USB-work needed:
http://www.infoplan.com.au/splitter/


On Thu, 12 Jul 2007 14:09:14 -0500, "Kelvin Beaton" <kelvin at mccsa
dot com> wrote:

>I'm getting the idea that USB will be required.... ;-)
>
>Thanks
>
>Kelvin
>
>
>"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
>news:(E-Mail Removed)...
>> On Thu, 12 Jul 2007 10:41:45 -0500, "Kelvin Beaton" <kelvin at mccsa dot
>> com>
>> wrote:
>>
>>>I have a field with names in it and I would like to get the first and last
>>>names, but the data looks like this.
>>>
>>>Jim Bob Brown
>>>Jim Billy Bob Brown
>>>Jim B Brown
>>>
>>>If I can assume that the first name is Jim and the last name is brown I
>>>could live with that.
>>
>> But you can't.
>>
>> My friend Darla Sue Jones (well, that's not her real last name) uses Darla
>> Sue
>> as her first name. It's not Darla, it's Darla Sue.
>>
>> My former coworker Felix de la Iglesia's last name is de la Iglesia. It's
>> not
>> Iglesia, and he would quite rightly object if addressed in that way.
>>
>> Parsing names requires a USB interface - Using Someone's Brain!
>>
>> John W. Vinson [MVP]
>
--
John Nurick - Access MVP

Hi Kelvin,

I recently had to do something similar for a database (created by another
member of staff) that had the whole name in one field, and we wanted
FirstName and SurName fields.

I exported the relevant table into Excel, then used DATA - TEXT TO COLUMNS
to split the field into bits (used the space between each name as the split)

Then i just took a few minutes of time to scan through the list results.
Most of the names had split fine, and for maybe 5% i just adjusted them to
look the way the should, it didn't take too long and it was a long list!

Then i imported everything back into access.

It was slightly fiddly, but not too much.

Let us know what you end up doing,

Kirstie

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> This can reduce but not eliminate the amount of USB-work needed:
> http://www.infoplan.com.au/splitter/
>
>
> On Thu, 12 Jul 2007 14:09:14 -0500, "Kelvin Beaton" <kelvin at mccsa
> dot com> wrote:
>
>>I'm getting the idea that USB will be required.... ;-)
>>
>>Thanks
>>
>>Kelvin
>>
>>
>>"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
>>news:(E-Mail Removed)...
>>> On Thu, 12 Jul 2007 10:41:45 -0500, "Kelvin Beaton" <kelvin at mccsa dot
>>> com>
>>> wrote:
>>>
>>>>I have a field with names in it and I would like to get the first and
>>>>last
>>>>names, but the data looks like this.
>>>>
>>>>Jim Bob Brown
>>>>Jim Billy Bob Brown
>>>>Jim B Brown
>>>>
>>>>If I can assume that the first name is Jim and the last name is brown I
>>>>could live with that.
>>>
>>> But you can't.
>>>
>>> My friend Darla Sue Jones (well, that's not her real last name) uses
>>> Darla
>>> Sue
>>> as her first name. It's not Darla, it's Darla Sue.
>>>
>>> My former coworker Felix de la Iglesia's last name is de la Iglesia.
>>> It's
>>> not
>>> Iglesia, and he would quite rightly object if addressed in that way.
>>>
>>> Parsing names requires a USB interface - Using Someone's Brain!
>>>
>>> John W. Vinson [MVP]
>>
> --
> John Nurick - Access MVP

sorry, just realised what newsgroup you were posting in, you were probably
not looking for something so basic!!

"Kirstie Adam" <(E-Mail Removed)(nospam)> wrote in message
news:(E-Mail Removed)...
> Hi Kelvin,
>
> I recently had to do something similar for a database (created by another
> member of staff) that had the whole name in one field, and we wanted
> FirstName and SurName fields.
>
> I exported the relevant table into Excel, then used DATA - TEXT TO COLUMNS
> to split the field into bits (used the space between each name as the
> split)
>
> Then i just took a few minutes of time to scan through the list results.
> Most of the names had split fine, and for maybe 5% i just adjusted them to
> look the way the should, it didn't take too long and it was a long list!
>
> Then i imported everything back into access.
>
> It was slightly fiddly, but not too much.
>
> Let us know what you end up doing,
>
> Kirstie
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> This can reduce but not eliminate the amount of USB-work needed:
>> http://www.infoplan.com.au/splitter/
>>
>>
>> On Thu, 12 Jul 2007 14:09:14 -0500, "Kelvin Beaton" <kelvin at mccsa
>> dot com> wrote:
>>
>>>I'm getting the idea that USB will be required.... ;-)
>>>
>>>Thanks
>>>
>>>Kelvin
>>>
>>>
>>>"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
>>>news:(E-Mail Removed)...
>>>> On Thu, 12 Jul 2007 10:41:45 -0500, "Kelvin Beaton" <kelvin at mccsa
>>>> dot
>>>> com>
>>>> wrote:
>>>>
>>>>>I have a field with names in it and I would like to get the first and
>>>>>last
>>>>>names, but the data looks like this.
>>>>>
>>>>>Jim Bob Brown
>>>>>Jim Billy Bob Brown
>>>>>Jim B Brown
>>>>>
>>>>>If I can assume that the first name is Jim and the last name is brown I
>>>>>could live with that.
>>>>
>>>> But you can't.
>>>>
>>>> My friend Darla Sue Jones (well, that's not her real last name) uses
>>>> Darla
>>>> Sue
>>>> as her first name. It's not Darla, it's Darla Sue.
>>>>
>>>> My former coworker Felix de la Iglesia's last name is de la Iglesia.
>>>> It's
>>>> not
>>>> Iglesia, and he would quite rightly object if addressed in that way.
>>>>
>>>> Parsing names requires a USB interface - Using Someone's Brain!
>>>>
>>>> John W. Vinson [MVP]
>>>
>> --
>> John Nurick - Access MVP
>
>