spliting a field

[Guest]

Using an exported Q&A database (ASCII), we imported the data into an Access
table. We have a field called Name. Name consists of Last Name, First Name
Middle Name. How can I get these names into a Last Name field, First Name
field and a Middle name field?

Sorry for such a stupid question.

 

[Jeff Boyce]

First, create the fields in your table.

Next, create a query against the data, using the Left(), Mid(), Right(), and
InStr() functions to isolate each of the pieces. When that's working,
change the query to an update query and update each record with its First,
Middle & Last info.

Parsing human names in Access is not for the faint-of-heart. For instance,
are you ABSOLUTELY certain that every [Name] value includes a First, Middle
and Last Name? Do you have any hyphenated (or un-hyphenated) last names?
Do you have any nicknames? Do you have anyone without a middle name? Do
you have anyone who, like "Cher", goes by a single name?

Plan on USB (using someone's brain) after you have Access attempt to parse
the [Name] data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

Thanks for the info, but stupid me doesn't know how to do that. can you
give me some examples?

Thanks,
Angel

First, create the fields in your table.

Next, create a query against the data, using the Left(), Mid(), Right(), and
InStr() functions to isolate each of the pieces. When that's working,
change the query to an update query and update each record with its First,
Middle & Last info.

Parsing human names in Access is not for the faint-of-heart. For instance,
are you ABSOLUTELY certain that every [Name] value includes a First, Middle
and Last Name? Do you have any hyphenated (or un-hyphenated) last names?
Do you have any nicknames? Do you have anyone without a middle name? Do
you have anyone who, like "Cher", goes by a single name?

Plan on USB (using someone's brain) after you have Access attempt to parse
the [Name] data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Using an exported Q&A database (ASCII), we imported the data into an
Access
table. We have a field called Name. Name consists of Last Name, First
Name
Middle Name. How can I get these names into a Last Name field, First Name
field and a Middle name field?

Sorry for such a stupid question.

 

[Jeff Boyce]

Create a new query. Add the table. Add the field.

Add a new field:
NewFirstNameField: Left([YourFullNameField],Instr([YourFullNameField],"
")

This takes the value in [YourFullNameField], starts at the left, and goes to
the (first) space. This becomes the value for [NewFirstNameField].

Take a look in Access HELP for syntax and examples for Left(), Mid(),
Right() and Instr() functions. Also, search on-line for "parse name".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the info, but stupid me doesn't know how to do that. can you
give me some examples?

Thanks,
Angel

First, create the fields in your table.

Next, create a query against the data, using the Left(), Mid(), Right(),
and
InStr() functions to isolate each of the pieces. When that's working,
change the query to an update query and update each record with its
First,
Middle & Last info.

Parsing human names in Access is not for the faint-of-heart. For
instance,
are you ABSOLUTELY certain that every [Name] value includes a First,
Middle
and Last Name? Do you have any hyphenated (or un-hyphenated) last names?
Do you have any nicknames? Do you have anyone without a middle name? Do
you have anyone who, like "Cher", goes by a single name?

Plan on USB (using someone's brain) after you have Access attempt to
parse
the [Name] data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Using an exported Q&A database (ASCII), we imported the data into an
Access
table. We have a field called Name. Name consists of Last Name, First
Name
Middle Name. How can I get these names into a Last Name field, First
Name
field and a Middle name field?

Sorry for such a stupid question.