How do I break an Access field into pieces (ie ( [Mrs & Mrs] [Joh.

B

Bnolt10s

I've "inherited" a database where the Title and First Name are a single
field. Is there an easy way to break them into 2 field?
 
W

Wayne Morgan

Create a new field (or 2 if you prefer) for the Title and, if 2, for the
First Name. Run an Update Query to update these fields to the new values.
You will use a calculated field in the query to do the update.

This should work as long as there aren't any spaces in the first names
themselves. If you go into 2 new fields, your old field will still be there
as a backup until you verify that everything worked correctly. If you update
the old field, then I would do this one field at a time. Create the title
field first and verify that it is correct, then update the old first name
field.

For the Title field
UPDATE tblMyTable Set tblMyTable.Title = Trim(Left([OldField],
InStrRev([OldField], " ")))

For the First Name field
UPDATE tblMyTable Set tblMyTable.FirstName = Trim(Mid([OldField],
InStrRev([OldField], " ")))
 
Top