Arrays 2

[Ouch]

can ayone help with arrays very new to this..

i am trying to write a function that splits a string so i can add the data seperately into a table


this is what i tried but i dont understand arrays

Private Sub Command44_Click()

Dim field As String
Dim sngArray As String

field = [authors]

sngArray = Split(field, ";&quot

Debug.Print sngArray(0)
Debug.Print sngArray(1)
Debug.Print sngArray(2)

End Sub

please help

 

[FoxProProgrammer]

Is the data in the string comma delimited? I don't understand what you mean by split the data. Are you trying to separate a field that is delimited into separate values in an array?

dz

 

[Ouch]

the string is seperated by a ;

eg

bloggs j ; doe j ; bathgate b ;

i need to seperate this into rows to put into a table

1 bloggs j
2 doe j
3 bathgate b


thanks for your reply.
i was told to use the split fuction but the help file is very vague...

 

[FoxProProgrammer]

If you want to use the split function, which returns an array, your declaration needs to look like this:

Dim sngArray(size) As String

You could count the number of semicolons in each string, and dimension your array to that value. Another option is to use the Left(), Mid(), Right(), and Instr() functions. That's how I normally do this sort of thing. You still need to write the code to open the table, add a record, and store the values. I assume that these will be new records added to a table? If so, do the following:

Dim Dbs As DAO.Database
Dim rst As DAO.Recordset

rst.AddNew
rst!<field>= <delimited string>
rst.Update

You can make up your own names for Dbs and rst if you want. Let me know how it goes and if you need more examples

 

[Ouch]

this is what i did, it comes up with the error
user defined type not defined

how do i tel it wich table i want to put it into

Private Sub Command44_Click()

Dim x As Variant
Dim i As Long
Dim Dbs As DAO.Database
Dim rst As DAO.Recordset

x = Split([authors], &quot;;&quot
For i = 0 To UBound(x)

[test] = [test] & x(i)'test the output

rst.AddNew
rst![Data] = x(i)
rst![tpref_id] = &quot;AUT&quot;
rst.Update
Next i
End Sub

 

[FoxProProgrammer]

Oops, I forgot to give you the Set statements:

Set Dbs = CurrentDb
Set rst = Dbs.OpenRecordset(&quot;<tablename>&quot;, dbOpenDynaset)

I haven't ever used the Split function and don't really know what it is used for. If you can get it to return an array with the values all separated into an array, it would be a convenient method. To add the values from the array to the table put it in a loop that loops from 1 to the number of items in the array...similar to what you have above.

 

[Ouch]

works....thankyou

this code will take data seperated by a ;(or a space or any other characters you care to add in)in one field and paste each part as a new record


completed code

Private Sub Command44_Click()

Set Dbs = CurrentDb
Set rst = Dbs.OpenRecordset(&quot;tbllookups&quot
Dim x As Variant
Dim i As Long


x = Split([authors], &quot;;&quot
For i = 0 To UBound(x)
rst.AddNew
rst![Data] = x(i)
rst![tpref_id] = &quot;AUT&quot;
rst.Update
Next i

End Sub

thank you very much

 

[FoxProProgrammer]

Thanks, Ouch!

I hadn't seen the Split function until you brought it to my attention. Whenever I parse strings, I would do the following:

Dim position As Integer
Dim Result(2) As String
Dim Db As DAO.Database
Dim rec As Recordset

position = InStr(1, ParseStr, &quot;;&quot
Result(1) = Left(ParseStr, position - 1)
Result(2) = Right(ParseStr, Len(ParseStr) - position)

Set Db = CurrentDb
Set rec = Db.OpenRecordset(&quot;ParseExample&quot;, dbOpenDynaset)

For i = 1 To 2
rec.AddNew
rec![ParseStr] = Result(i)
rec.Update
Next i

End Sub

This is a simple example when there are only two values in a string separated by a delimeter. If there are more values, I put it in a loop and use the Mid() function to parse the strings in the middle of the string. The Split() function definitely simplifies the code. I'll try to integrate it into one of my applications later. Thanks for posting your code, and thanks for the star.

dz