Splitting a string in one field to multipe fields in another table

[KP_SoCal]

This is somewhat complicated. For Table1, each record of text Field1 has string length in multiples of 3 that is no greater than 12 characters and no less than 3 characters.

For instance:
Table1

Record1 = ABC123ABC
Record2 = 123
Record3 = ABC123
Record4 = 123ABC123ABC

In a query (perhaps an Append Query?), I would like to append these records into Table2 by splitting them by multiples of 3 into separate records. Since the maximum amount of characters in a given string is 12 characters, I only need Table2 to have 4 different field names. The results would look something like this.

Table2


Record1
Field1 = ABC; Field2 = 123; Field3 = ABC; Field4 = IsNull

Record2
Field1 = 123; Field2 = IsNull; Field3 = IsNull; Field4 = IsNull

Record3
Field1 = ABC; Field2 = 123; Field3 = IsNull; Field4 = IsNull

Record4
Field1 = 123; Field2 = ABC; Field3 = 123; Field4 = ABC

I’m fairly certain this can only be accomplished with a function created inside of a module that is ran within my query. I’m using Access 2007. Thanks so much for any suggestions!

 

[John Big Booty]

I suspect that the Replace() function will will do the trick on this one as well.

 

[raskew]

Hi -

Try this select query, replacing table and field names as necessary. When satisified, convert it to an append query.

SELECT
    Field1
  , Left([Field1],3) AS Fld1
  , IIf(Len([Field1])>3,Mid([Field1],4,3),"") AS Fld2
  , IIf(Len([Field1])>6,Mid([Field1],7,3),"") AS Fld3
  , IIf(Len([Field1])>9,Mid([Field1],10,3),"") AS Fld4
FROM
   Table9;

Results (using your sample data)

Field1          Fld1    Fld2    Fld3    Fld4
ABC123ABC       ABC     123     ABC 
123             123         
ABC123          ABC     123     
123ABC123ABC    123     ABC     123     ABC

HTH - Bob

 

[KP_SoCal]

Raskew, this works great! Thanks so much for your help!!!

 

[raskew]

Great! Glad it helped.

Best Wishes - Bob