convert text into table

[Machiaveli]

Hi everyone,

I have a problem with converting text into tables.

What i want to do is to convert the following text in a field:

{"LA";"NY";"SA"}

into a table:
LA
NY
SA

Can someone help me?

 

[dyarwood]

Something like

Dim rs As Recordset
a() = {"LA","NY","SA"}
j = Len(a()) 'Not sure on this, length of array
rs.MoveFirst
Do Until i > j
rs.Fields(0)=a(i)
rs.MoveNext

Loop

Not sure if that helps let me know

dyarwood

 

[Machiaveli]

Hi,

Thnx for the code, but i can't get it to work. Something goes wrong from the a().

Can you give me a working code?

 

[dyarwood]

Dim rs As New ADODB.Recordset

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim a(3) As String
a(0) = "LA"
a(1) = "NY"
a(2) = "SA"
rs.Open "TABLE NAME", cnn, adOpenDynamic, adLockOptimistic
Do Until i > j
j = 3 'length of array
rs.MoveFirst
x = a(i)
rs.AddNew
rs.MoveLast
rs.Fields(0) = x
i = i + 1
Loop

Something like this should be close to working I think

dyarwood

 

[oharab]

dyarwood's code is almost there, but it assumes that you already have the elements of the string individually, where as what Machiaveli has is {"LA";"NY";"SA"} already in a field.
What you need to do is remove the { and } then uses the Split function to create an array which you can then use with dyarwood's code. hint: you'll need to use ubound to get a value for j!.

Have a go and if you get stuck, post the code you have so far and we can take a look at it.

Good luck

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[Machiaveli]

I can't make the code work. I probably need to learn more code.

Can someone help me complete it?