Split 1 field into 2.

  • Thread starter Thread starter Confounded
  • Start date Start date
C

Confounded

I have a Name field which I wish to split into LastName
and FirstName fields. I can't find any help at all on
this. I tried making a macro but when it tries to execute
the Shift-INS command to paste the automatically-copied
last name into a new field, I get a message about an
Access Utility Add-In that apparently doesn't actually
exist except in the imagination of my Office Assistant (I
can't find it in the setup program, or any mention in the
documentation or even the MS web site).

How would you go about this?

Thanks in advance.
 
How would you go about this?
Click to expand...

With an Update Query. Macros are not appropriate, and neither is copy
and paste! As a relational database, Access uses Queries for this kind
of data manipulation.

If you have a table containing fields Fullname (containing say "John
Vinson"), and FirstName and LastName (currently empty), you can create
a Query based on the table. Change it to an Update query using the
Query menu or the query type icon. Select the FirstName and LastName
fields.

On the UpdateTo line under Firstname put

Left([fullname], InStr([fullname], " ") - 1)

and under Lastname put

Mid([fullname], InStr([fullname], " ") + 1)

The InStr function finds the position of the first blank in the
Fullname field; Left and Mid pull off the portions of the name to the
left and right of that blank.

Note that names such as

Joe Bob Bloggs

will give incorrect results; you might want to run a search using

LIKE "* *"

as a criterion on LastName after you've run the update query to see if
any names need fixing.
 
You can write a simple function that will return the first name and last
name
is the data in the format FirstName<space>LastName
What about middle initial?

HS
 
There are two ways to handle this... and I am not sure in which would be
simpler in your case... If you wanted to do this through a recordset...
probably the best method would be to have an open rs to the existing data
and a 2nd rs to the new table where you want to dump the results...
if this is the option you want to use, just do something like

while not rs1.eof
rs2.addnew
rs2!Lastname = split(rs1!FullName)(ubound(split(rs1!FullName))) 'Use the
UBOUND() function to make sure you get the last "name" in the field
rs2!Firstname = split(rs1!FullName)(0)
rs2.update
rs1.movenext
loop

if you want to do this with one wave of the magic wand through a
all-powerful SQL statement... you might have a bit of a time trying to do
it...
you could probably do something like:
select *, left(FULLNAME,charindex(FULLNAME," ")-1) as FirstName,
right(FULLNAME,len(FULLNAME) - charindex(FULLNAME," ")) as LastName

you'll probably have to modify these statements to meet what you are trying
to do... but this should at the least give you a direction in which to
work.
 
Back
Top