Is there a way to split a field (full name) into 2 fields (first,.

J

Jon Furman

Sure you could do some manipulations using the VBA string functions, but a
smarter idea would be to store the First and Last names seperately and then
concatenate them when needed.


Jon
 
K

Ken Snell [MVP]

Yes. Create two new fields in your table, and then run an update query that
parses the full name field into the data desired for the other two fields.
Then delete the full name field from your table.
 
M

Mike Labosh

Is there a way to split a field in access, like full name, into 2 fields?

I don't know how complex or clean your names are, but name parsing can be
tricky. I have a 7 page algorithm and several lookup tables that is my
name-parsing subsystem with several rules:

Below are some comments and examples taken from my code that might give you
some examples. We store contactnames as FirstNamePrefix, FirstName,
MiddleName, LastName, LastNameSuffix

' Rule 1: Clean the name (has its own internal rules)
' "ATTN Mr Maj @#$%^* }{ Gen.Robert (Bob) Stevens van ter
Hoffen-Schlager Sr. MD, Computer Guy"
' -> "ATTN Mr Maj Gen. Robert Stevens van ter Hoffen-Schlager Sr. MD"

' Rule 2: Remove anything that's in the dropwords list.
' "ATTN Mr Maj Gen. Robert Stevens van ter Hoffen-Schlager Sr. MD" ->
' "Mr Maj Gen. Robert Stevens van ter Hoffen-Schlager Sr. MD"

' Rule 3: Move all tokens that are salutations into the prefix string
' stop at the first token that is not a salutation.
' "Mr Maj Gen. Robert Stevens van ter Hoffen-Schlager Sr. MD"
' -> "Robert Stevens van ter Hoffen-Schlager Sr. MD"
' prefix -> "Mr Maj Gen."

' Rule 4: Move all tokens that are last name suffixes into the suffix
' string stop at the first token that is not a last name suffix.
'"Robert Stevens van ter Hoffen-Schlager Sr. MD"
' -> "Robert Stevens van ter Hoffen-Schlager"
' suffix -> "Sr. MD"

' Rule 5: Move all tokens that are last name prefixes into the beginning
' of the last name. Stop at the first one that is not a prefix.
' "Robert | Stevens | van | ter | Hoffen-Schlager"
' -> "Robert | Stevens | van ter Hoffen-Schlager"

' Rule 6: Define First, Middle & Last names thusly:
' Remove all zero length tokens
' Count tokens
' If only one, it is last name
' If two, assign to FirstName, LastName
' If three, assign to FirstName, MiddleName, LastName
' If three or more, first token is FirstName, last token is LastName
'everything else is the MiddleName

' Rule 7: Drop last name if NA, NK, NN, NONE, NOT, or only one character

' Rule 8: Drop last name if all the same character

' Rule 9: Truncate to fit
' Truncate to fit the database column and prevent truncation errors.
 
T

Tom Wickerath

As Ken indicates, you can run an update query to parse the full name into two fields. There are
numerous posts in this newsgroup that include example queries. You'll use functions such as
InStr, Len, Left, etc. to do this within Access. This may work satisfactory for you, if your name
data is fairly clean. I find it easier to export the data to Excel, and then use Excels Data >
Text to columns... functionality to split the data. Then simply re-import it into Access.

On the other hand, if your full name data includes salutations, suffixes, etc., or is not in a
consistent order, you may want to look into using Splitter for Microsoft Access:
http://www.infoplan.com.au/splitter/


Tom
__________________________________


Yes. Create two new fields in your table, and then run an update query that
parses the full name field into the data desired for the other two fields.
Then delete the full name field from your table.
--

Ken Snell
<MS ACCESS MVP>

__________________________________


Is there a way to split a field in access, like full name, into 2 fields?
 
X

Xazn

Still unsure how to split field to First name and Last name. example is
"Smith, John" is one field. Sorry for not understanding.
 
M

Mike Labosh

Still unsure how to split field to First name and Last name. example is
"Smith, John" is one field. Sorry for not understanding.
Click to expand...

1. Make a new query in design view and cancel the add table dialog.
2. Switch to SQL View and delete what you see there.
3. Paste this

UPDATE YourTable
SET FirstName = Right$(FullName, Len(FullName) - (Instr(FullName, ", ")
+ 2),
LastName = Left$(FullName, Instr(FullName, ", ") - 1)

4. Change the name of your table, and the names of the FullName, FirstName
and LastName
 
T

Tom Wickerath

Try this if you want to do it within Access:

First, add two new text fields to your table that includes the name data. Name these fields
LastName and FirstName. Specify a reasonable length for each field (say 20~25 characters). Then,
create a new update query. Add the table that contains your name data. In query design view,
click on Query > Update Query to change it from a SELECT query to an UPDATE query.

LastName
Update To: Left([FullName],InStr([FullName],",")-1)

FirstName
Update To: Mid([FullName],InStr([FullName],",")+2)

where FullName is the name of the field that contains the full name. If this field has a
different name, then make the appropriate adjustment in the Update To: rows shown above.


Tom
____________________________________


Still unsure how to split field to First name and Last name. example is
"Smith, John" is one field. Sorry for not understanding.
 
Top