Split info in one field into two fields?

  • Thread starter Thread starter Stranger
  • Start date Start date
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.
 
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.
 
Access is saying that I am missing a closing parenthesis, bracket, or
vertical bar.
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top