Splitting data in a field

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

I have inherited a database where the tables have a field
name of "Name" and the data was typed in as
i.e 'Smith,John'.

I know I could export the table(s) into Excel split the
data and then import the table back into Access.

Can this be done within Access without doing the steps as
described above split the data so there is a Last Name
field with corresponding data and a First Name field with
corresponding data?
 
I have inherited a database where the tables have a field
name of "Name" and the data was typed in as
i.e 'Smith,John'.

I know I could export the table(s) into Excel split the
data and then import the table back into Access.

Can this be done within Access without doing the steps as
described above split the data so there is a Last Name
field with corresponding data and a First Name field with
corresponding data?
Click to expand...

Yes. Run an Update query updating LastName to

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

and FirstName to

Trim(Mid([Name], InStr([Name], ",") + 1))
 
Robert

After doing the update John V. suggests, consider getting rid of your field
named "Name" -- that's a reserved word in Access and will only give Access
(and you) headaches!

As always, make a backup first!
 
Back
Top