split data in a field into two fields [General]

From: reillc01 on 22 Mar 2010 08:22

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!

From: Duane Hookom on 22 Mar 2010 08:32

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.

--
Duane Hookom
MS Access MVP

"reillc01" <reillc01(a)discussions.microsoft.com> wrote in message
news:F3D3CC6C-D917-4D74-8D02-1EA762F759DD(a)microsoft.com...
> 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!

From: 136899587 on 22 Mar 2010 11:06

"reillc01" <reillc01(a)discussions.microsoft.com> 写入消息
news:F3D3CC6C-D917-4D74-8D02-1EA762F759DD(a)microsoft.com...
> 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!

From: 136899587 on 22 Mar 2010 11:08

|
Pages: 1
Prev: hi group
Next: Access Issues