Splitting a Field

[DanAuber]

Hi,

I'd be grateful for some idiot help here.

I have a table with 1 field that looks like this:

asdsag\8787878\99999
ss\87878sdsdr\dadsad

etc...

there is no set length of the strings between the slashes

Now I want to run a command that picks up my table1 and ends up creating a new table (or inserting into a new table). The new table would have 3 fields each populated with the strings separated by the slashes. (essentially I'm talking about the "text to columns" functionality from excel)

I know I can use the SPLIT function, but have no idea exactly what my code needs to look like to do the task as I have explained it above.

Thanks for any help on this

dan

Dan Auber

 

[JoeEskimo]

If you use the menu File, Get External Data, Import and tell it the import file is Delimited, then you can also tell it that it is delimited by "\" and the result of the imported file will be what you want.

You can create a macro of the above description in case you need to import this file on a regular basis.

 

[Zion7]

as far as the Split() function goes....

It creates an array, of variant type, with the number of elements, equal to number of "delimiters" + 1.

In regards to your whole objective here, I would first open a recordset, of the table, you are getting the info from...

Dim rec As ADODB.Recordset, vArray As Variant

Set rec = New ADODB.Recordset
rec.Open "Table1", CurrentProject.Connection, adOpenDynaset, adLockOptimistic

Do Until rec.EOF

vArray = Split(rec!Field1,"\") 'creates a 3 element, array

DoCmd.RunSQL "INSERT INTO "Table2" ("Field1", "Field2", "Field3")
VALUES (vArray(0), vArray(1), vArray(2))
rec.MoveNext
Loop

rec.Close: Set rec = Nothing

Hope this Helps, good luck!