William Lawrence said:
Access 2007
I have a [Name] field with both first and last Name. I would like to
separate them into 2 fields [First Name] & [Last Name]. Can this be done?
This group is really more for template questions but since you're here, the
easiest way to do this is to use Excel. Copy the table (if it's not too
large) and paste it into an Excel worksheet. or use the Send to Excel for
Analysis command. Insert enough columns to the right of your Name field to
accommodate the new field plus an extra to be sure. It can be deleted later.
Use the Data/Text to Columns wizard to split the field into two columns. Use
the Delimited setting and use Space when asked for the separator. Then
copy/paste the data back to Access. You can select the entire table range in
Excel, copy it, navigate to Access, right-click in the Tables list in the
Navigation pane, and click Paste. You'll be asked if the first row contains
your column heading and then click Yes. Remove the previously created table
since you now have a duplicate. Make sure you rename the newly created table
the same as your old table if you have other Access objects based on it and
recreate any relationships if necessary.
If your table is too large to copy/paste then you can do this in Access as
well using a couple queries.. If you encounter this, post in one of the
Access newsgroups and they should be able to help you out with the necessary
functions for separating the field and the instructions on how to use an
update query to update your existing table with the new data. They may
provide a VBA solution as well. Either method will work.
--
~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP?
http://mvp.support.microsoft.com/gp/mvpfaqs
Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx