split data in a field into two fields

[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!

 

[Duane Hookom]

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.

 

[Guest]

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!

 

[Guest]

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!