split data in a field into two fields

  • Thread starter Thread starter reillc01
  • Start date Start date
R

reillc01

I importated a db from ms works into access. I wold like to seperate the data
from one field into two different fields what's the easiest way to do this
without resorting to manual entry?

Eg department field should be department and location fields
200 1786 becomes
200 in one department field and
1786 in location field
Thanks!
 
I would first back up the database and then use an update query. If your
values are all consistent with a single space between the Department and
Location, your syntax might look like:

UPDATE tblMSWorksImport
SET Department = Left(DeptLoc, Instr(DeptLoc, " ") -1),
Location = Mid(DeptLoc, Instr(DeptLoc, " ") +1);

You would need to substitute your actual field and table names since you
made me guess.
 
reillc01 said:
I importated a db from ms works into access. I wold like to seperate the
data
from one field into two different fields what's the easiest way to do this
without resorting to manual entry?

Eg department field should be department and location fields
200 1786 becomes
200 in one department field and
1786 in location field
Thanks!
Click to expand...
 
reillc01 said:
I importated a db from ms works into access. I wold like to seperate the
data
from one field into two different fields what's the easiest way to do this
without resorting to manual entry?

Eg department field should be department and location fields
200 1786 becomes
200 in one department field and
1786 in location field
Thanks!
Click to expand...
 
Back
Top