Splitting field

[sueviolet]

I would like to separate values in one field into mutiple fields. All values are separated by a space and the number of values vary.

An example:

0300194 0300092 0300091 0300090

Can anybody help me with the code need to split my field up?

Thanks

 

[FLabrecque]

This update FIELD2 with the first series of numbers from FIELD1:
UPDATE DUMMY_TABLE
SET FIELD2 = mid([FIELD1], 1, InStr([FIELD1], " "))

This remove the first series of numbers from FIELD2:
UPDATE DUMMY_TABLE
SET FIELD1 = mid([FIELD1], InStr([FIELD1], " ") + 1)

Perform these 2, changing FIELD2 to FIELD3, FIELD4 and so on. It may not be the best solution. But it's a start on one!

NOTE: Perform a couple of checks before any updates. You might include by accident the blank space.

 

[RV]

Depending on the version of Access you use, you can use the Replace function to replace any spaces with null ("").
This requires you running just one update query:

UPDATE YourTable
SET YourColumn= Replace(YourColumn,Chr(32) ,"",1,10)

Replace the argument 10 (representing up to how many spaces have to be replaced) with whatever value as long as it exceeds your maximum number of spaces.

RV