need to extract specific characters from field

[MES via AccessMonster.com]

I have a field in a table that lists a person's name as [Last Name,First Name]
.. So the way a name appears in the field is Doe,John. I would like to build
two expressions that would separate out both the last name and the first name
from that field, so that I would then have 2 separate fields.

Is there a way to do this?

Thanks in advance.

 

[Jeff Boyce]

First, consider modifying the table structure. If you have a need to have
these two separate values, store these two separate values in two separate
fields.

To parse the current string, one approach would be to use a query and the
Left(), Instr(), and Mid() functions to find the string to the left of the
comma (",") and the string that follows the comma. Then, following the
above suggestion, use this to do an update query on the table's new fields!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Field: LastName: Left([TheNameField],Instr(1,[TheNameField],",")-1)

Field: FirstName:Mid([TheNameField],Instr(1,[TheNameField],",")+1)

Those will both fail if the is no comma in your name field.

So, if the name field might have no comma or be blank you might want to use
a slightly more complex expression

Field: LastName: IIF([TheNameField] & "" Like "*,*"
,Left([TheNameField],Instr(1,[TheNameField],",")-1),[TheNameField])
Field: FirstName: IIF([TheNameField] & "" Like "*,*"
,Mid([TheNameField],Instr(1,[TheNameField],",")+1), Null)

 

[MES via AccessMonster.com]

Thanks for your prompt response. That solution seems to work for me.
However, I just realized that some of my data has a space after the first
name, then the middle initial. For example "Doe,John A". How would I
isolate just the first name, not the first name and the middle initial?

Field: LastName: Left([TheNameField],Instr(1,[TheNameField],",")-1)

Field: FirstName:Mid([TheNameField],Instr(1,[TheNameField],",")+1)

Those will both fail if the is no comma in your name field.

So, if the name field might have no comma or be blank you might want to use
a slightly more complex expression

Field: LastName: IIF([TheNameField] & "" Like "*,*"
,Left([TheNameField],Instr(1,[TheNameField],",")-1),[TheNameField])
Field: FirstName: IIF([TheNameField] & "" Like "*,*"
,Mid([TheNameField],Instr(1,[TheNameField],",")+1), Null)

I have a field in a table that lists a person's name as [Last Name,First
Name]

[quoted text clipped - 7 lines]

Thanks in advance.

 

[Jeff Boyce]

Extend the approach John S. provided. You'll be looking for the space (" ")
on the "inside" string.

Beware, however, if there is NO space, you'll get an error. You may want to
test for if there is a space (using IIF() function) before then processing
those that do have a space.

My original suggestion was to have one field in your table for each piece of
data you wish to keep. If you are going to split out MiddleName/Initial,
you'll need a field for that as well.

And how do you propose to handle names like:

Cher
William van de Wigge
Billy Bob Thornton, Jr.

?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your prompt response. That solution seems to work for me.
However, I just realized that some of my data has a space after the first
name, then the middle initial. For example "Doe,John A". How would I
isolate just the first name, not the first name and the middle initial?

Field: LastName: Left([TheNameField],Instr(1,[TheNameField],",")-1)

Field: FirstName:Mid([TheNameField],Instr(1,[TheNameField],",")+1)

Those will both fail if the is no comma in your name field.

So, if the name field might have no comma or be blank you might want to
use
a slightly more complex expression

Field: LastName: IIF([TheNameField] & "" Like "*,*"
,Left([TheNameField],Instr(1,[TheNameField],",")-1),[TheNameField])
Field: FirstName: IIF([TheNameField] & "" Like "*,*"
,Mid([TheNameField],Instr(1,[TheNameField],",")+1), Null)

I have a field in a table that lists a person's name as [Last Name,First
Name]

[quoted text clipped - 7 lines]

Thanks in advance.