Dividing one text field into several text fields.

[Frank]

I have an access database with a text field that reflects names: eg. first
name, last name, ini, prefix - Doe, John, M., Mr.

How can I globally break this field into individual fields such as:
[firstname],[lastname],[ini],[prefix]?

I look forward to your professional assistance.

 

[Jeff Boyce]

"Professional" costs more -- you've reached a newsgroup frequented by
volunteers <g>!

Breaking a "name" field into its components is a very good idea, ... and a
very hard task. Try searching mvps.org/access or Google.com or these
newsgroups for this topic, also called "parsing". The real-world problem is
that not all names are equal.

For instance, even if you were able to work out a method of breaking that
name field by commas and spaces, you'd still have to deal with the
difference among names like:

Doe, John
Jingleheimer-Schmidt, John Jacob
van der Wigge, Jane
Roberts, Jr., Robert R.
Smith y Herrera, Ms. Elaina Maria Octavia

Take your best shot at breaking these apart, then plan on employing USB --
Using Someone's Brain -- to do the final review/revision.

One possibility ... import the names into Excel and use the Excel function
that parses into separate fields. Then go through all the names and move
the pieces into the correct columns. Then import the result into Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[fredg]

I have an access database with a text field that reflects names: eg. first
name, last name, ini, prefix - Doe, John, M., Mr.

How can I globally break this field into individual fields such as:
[firstname],[lastname],[ini],[prefix]?

I look forward to your professional assistance.

If you have Access 2000 or newer:

Make sure the data layout is exactly as you show it.

You indicagte it's in FirstName, LastName, etc. order, however your
example shows LastName, FirstName, etc. order.
Let's work with LastName, FirstName order.

If a name if missing, leave it's comma, i.e. Smith, Joseph, , Dr.

Copy and Paste the following into a Module:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)

End Function
===========

Make a back-up table first.

Then paste this SQL into a new Query.
Change YourTable and field names to what ever the actual table name
and field names are.

Update YourTable Set YourTable.[LastName] =
Trim(ParseText([FullName],0)),YourTable.[FirstName] =
Trim(ParseText([FullName],1)),YourTable.[MI] =
Trim(ParseText([FullName],2)),YourTable.[Prefix] =
Trim(ParseText([FullName],3)) Where YourTable.[FullName] Is Not Null;

 

[Frank]

Thanks all for most helpful and professinal suggestions.
All worked perfectly.

Thanks once again.

I have an access database with a text field that reflects names: eg.
first
name, last name, ini, prefix - Doe, John, M., Mr.

How can I globally break this field into individual fields such as:
[firstname],[lastname],[ini],[prefix]?

I look forward to your professional assistance.

If you have Access 2000 or newer:

Make sure the data layout is exactly as you show it.

You indicagte it's in FirstName, LastName, etc. order, however your
example shows LastName, FirstName, etc. order.
Let's work with LastName, FirstName order.

If a name if missing, leave it's comma, i.e. Smith, Joseph, , Dr.

Copy and Paste the following into a Module:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)

End Function
===========

Make a back-up table first.

Then paste this SQL into a new Query.
Change YourTable and field names to what ever the actual table name
and field names are.

Update YourTable Set YourTable.[LastName] =
Trim(ParseText([FullName],0)),YourTable.[FirstName] =
Trim(ParseText([FullName],1)),YourTable.[MI] =
Trim(ParseText([FullName],2)),YourTable.[Prefix] =
Trim(ParseText([FullName],3)) Where YourTable.[FullName] Is Not Null;