Split info in one field into two fields?

S

Stranger

Currently employee names are in the field empfirstname.
Is there an easy way to move the employee last names into the field emplastname?

thanks.
 
A

Allen Browne

Make a backup in case something goes wrong, and use an Update query to
populate the LastName field and remove the surname from the first name
field.

1. Create a query into your table.

2. Change it to an Update query: Update on Query menu.

3. In the Update row under your LastName field, enter:
Trim(Mid([EmpFirstName], Instr([EmpFirstName], " ") + 1)

4. Run the query.

5. After verifying that the LastName field has populated correctly, remove
the entry under the LastName field, and enter this into the Update row under
the EmpFirstName field, to remove the surname from this field:
Trim(Left([EmpFirstName], Len([EmpFirstName]) - Nz(Len([LastName]),0)))

6. Run the query.

Instr() locates a string within a field - the space in this instance.
Mid() and Left() return part of the field.
Len() returns the number of characters in a string.
Trim() removes any leading or trailing spaces.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Currently employee names are in the field empfirstname.
Is there an easy way to move the employee last names into the field
emplastname?

thanks.
 
S

Stranger

Access is saying that I am missing a closing parenthesis, bracket, or
vertical bar.
 
A

Allen Browne

It is missing a bracket: 3 opening brackets and 2 closing won't work. Try:
Trim(Mid([EmpFirstName], Instr([EmpFirstName], " ") + 1))

Stranger said:
Access is saying that I am missing a closing parenthesis, bracket, or
vertical bar.

Allen Browne said:
Make a backup in case something goes wrong, and use an Update query to
populate the LastName field and remove the surname from the first name
field.

1. Create a query into your table.

2. Change it to an Update query: Update on Query menu.

3. In the Update row under your LastName field, enter:
Trim(Mid([EmpFirstName], Instr([EmpFirstName], " ") + 1)

4. Run the query.

5. After verifying that the LastName field has populated correctly,
remove the entry under the LastName field, and enter this into the Update
row under the EmpFirstName field, to remove the surname from this field:
Trim(Left([EmpFirstName], Len([EmpFirstName]) -
Nz(Len([LastName]),0)))

6. Run the query.

Instr() locates a string within a field - the space in this instance.
Mid() and Left() return part of the field.
Len() returns the number of characters in a string.
Trim() removes any leading or trailing spaces.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Currently employee names are in the field empfirstname.
Is there an easy way to move the employee last names into the field
emplastname?

thanks.
 
S

Stranger

That worked. Thanks. The second one to trim the last name from the
firstname field didn't work but I just did that part manually. there are
only 98 employees in the table so far.

thanks for all your help.

Allen Browne said:
It is missing a bracket: 3 opening brackets and 2 closing won't work. Try:
Trim(Mid([EmpFirstName], Instr([EmpFirstName], " ") + 1))

Stranger said:
Access is saying that I am missing a closing parenthesis, bracket, or
vertical bar.

Allen Browne said:
Make a backup in case something goes wrong, and use an Update query to
populate the LastName field and remove the surname from the first name
field.

1. Create a query into your table.

2. Change it to an Update query: Update on Query menu.

3. In the Update row under your LastName field, enter:
Trim(Mid([EmpFirstName], Instr([EmpFirstName], " ") + 1)

4. Run the query.

5. After verifying that the LastName field has populated correctly,
remove the entry under the LastName field, and enter this into the
Update row under the EmpFirstName field, to remove the surname from this
field:
Trim(Left([EmpFirstName], Len([EmpFirstName]) -
Nz(Len([LastName]),0)))

6. Run the query.

Instr() locates a string within a field - the space in this instance.
Mid() and Left() return part of the field.
Len() returns the number of characters in a string.
Trim() removes any leading or trailing spaces.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Currently employee names are in the field empfirstname.
Is there an easy way to move the employee last names into the field
emplastname?

thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top