access field that contains first name space last into separate fie

S

Sheila

How do I take a field in access that contains both first and last names of
clients, and split them into two new fields, first name and last name?
 
C

Chaim

Someone else asked a very similar question today in the public.access
newsgroup.

What do the names look like in the 'unified name' field? If they are a
consistent format, you can use the various string manipulation functions-
instr, mid, left, right, trim- or the array creating function (in VBA)
split() - to break them in two. If the format is not consistent, "Abandon
all hope ..."

Let us know what the names look like.
 
E

Eric D via AccessMonster.com

Search Help for Instr for starters.
You can search a string for any character... Instr will find the first
occurrence.
Example: Instr(1,"William Bonner", " ") should return 8
You can then use the mid function to find the first/second parts of the name.

Resulting Example:
Create a new query based on your Table
Add the following two new columns (only at this time)
Fname: Mid([YourNamefield],1,InStr(1,[YourNameField]," ")-1)
LName: Mid([YourNameField],InStr(1,[YourNameField]," ")+1)
Test/verify
Add other columns you want in your query results
Switch query to MakeTable and run.
If you want up Update your current table, you must Add the two column names
in design view first.
 
E

Eric D via AccessMonster.com

Forgot to mention... This won't work very well if you have more than one
space in your Full Name field but it gets you started and you should be able
to refine the query from there.
 
E

Eric D via AccessMonster.com

This will give you better results for the LastName

LName: Mid([Name],InStr(1,[Name]," ")+1)
Now, if you have Tom Jones Jr, Last Name will be Jones Jr

Sorry.....
 
Top