split a field

[David#]

I have a data table which has two sets of text with a space separating them.
I need to remove text left of the space and place into a new field. I'm
finding the count of characters to move using InStr function, but how do I
then delete and move to a new field??

dd

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way
you expect.

Use an UPDATE query.

UPDATE YourTable
SET ExistingField = LEFT([ExistingField],Instr(1,[ExistingField]," ")-1)
, NewField = Trim(Mid([ExistingField],Instr(1,[ExistingField]," ")+1))
WHERE ExistingField Like "* *"

In the query grid (design view)
-- add the existing field and the newfield
-- Select Query Update from the menu
-- enter Like "* *" in the criteria for the existing field
-- Type the following for update to under Existing field
---- LEFT([ExistingField],Instr(1,[ExistingField]," ")-1)
-- Type the following for Update to under NewField
---- Trim(Mid([ExistingField],Instr(1,[ExistingField]," ")+1))

Do use your field names for the existing field and the new field

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Stefan Hoffmann]

hi Davie,

I have a data table which has two sets of text with a space separating them.
I need to remove text left of the space and place into a new field. I'm
finding the count of characters to move using InStr function, but how do I
then delete and move to a new field??

This should work:

UPDATE [yourTable]
SET [newField1] = Left([oldField],
InStr([oldField], Chr(32)) - 1),
[newField2] = Mid([oldField],
InStr([oldField], Chr(32)) + 1,
Length([oldField]))


mfG
--> stefan <--

 

[Allen Browne]

Left(), Len() and Mid() should help.

Type something like this in the Field row:
InStr([Field1], " ")
substituting your field name for Field1.
In the Criteria row under this field, enter:

0

This eliminates records that don't have a space in this field.

In the next column in the Field row, enter:
Trim(Mid([Field1], InStr([Field1], " ") + 1))
Test. If this gives the right expression, change it to an Update query
(Update on Query menu), and use that expression in the Update row under the
new field.

Run the query. After verifying the results, you can remove the later part
from the original field by updating it to:
Left([Field1], Mid([Field1], InStr([Field1], " ") - 1)