how do I break up a field into multiple fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to break up the "Name" filed into 2 fields, namely "Last Name" and
"First Name"
example
Name - Smith, John

into

First Name - John
Last Name - Smith
 
If they all have the comma in them, you can use it to determine where to
split the data.

1) Create the two new fields.

2) Create an Update Query to take the data from the current field and use it
as the new value for the empty new fields.

For the Last Name field, the Update To should be
Left([Name], InStr([Name], ",") - 1)

For the First Name field, the Update To should be
Trim(Mid([Name], InStr([Name], ",") + 1))

You could use +2 in the first name instead of Trim to get rid of the space
between the comma and the start of the first name.

Just a caution, Name is a reserved word. You should be ok here if you
enclose it in brackets, but you should avoid the use of reserved words.
Reserved words are words that have meaning for Access. This would include
names of built in functions, names of properties and methods, names of built
in constants, etc.
 
Back
Top