SPlit Function Question

[rfuscjr via AccessMonster.com]

Alex was kind enough to point me toward this nifty little function but I am
stuck on sytax...I get an Expected array on this: (" & rstCCs!id &....portion.
..

Dim db As Database
Set db = CurrentDb
Dim rst As Recordset
Dim strSqlStmnt As String
Dim qdf As QueryDef
Dim rstCCs As DAO.Recordset
Dim rstTemp As DAO.Recordset
Dim blRet As Boolean
Dim I As Integer
Dim a As String
Set qdf = db.CreateQueryDef("")
DoCmd.SetWarnings False
Set rstCCs = db.OpenRecordset("ztest", dbOpenTable)
Set rstTemp = db.OpenRecordset("ztest2", dbOpenTable)

With rstCCs
.MoveFirst
Do Until rstCCs.EOF
a = Split(rst!FSC_LIST, "^")
For I = 0 To (a)
strSqlStmnt = "INSERT INTO ztest2 ( id, listitem, sequence ) Values("
& rstCCs!id & "," & a(I) & "," & I & ")"
qdf.SQL = strSqlStmnt
qdf.ODBCTimeout = 0
qdf.Execute
Next I
.MoveNext
Loop
End With
rstCCs.Close
MsgBox "Done!"

DoCmd.SetWarnings True

 

[Jim Burke in Novi]

You need to declare a as an array, e.g. Dim a() as string. Then change the
For statement to For i = 0 to UBound(a). It may be UBound(a) -1, not sure if
UBound returns a zero-based value or not. I think that should do it.

 

[Michel Walsh]

Use


Dim a( ) As String

rather than

Dim a As String


Vanderghast, Access MVP

 

[rfuscjr via AccessMonster.com]

thanks...will try...I guess I a Dim Wit!

Use

Dim a( ) As String

rather than

Dim a As String

Vanderghast, Access MVP

Alex was kind enough to point me toward this nifty little function but I
am

[quoted text clipped - 36 lines]

DoCmd.SetWarnings True

 

[Michel Walsh]

In VBA, the limits are inclusive, ie, LBound and UBound return index you
can use to access the first and the last element of the array.


For i = LBound(a) TO UBound(a)
...
Next i


For optimization purpose, in may be useful to cache the result of UBound(a),
otherwise, it is recomputed once at each iteration:



Dim n_upper As long : n_upper = UBound(a)
For i = LBound(a) To n_upper
...
Next i



Vanderghast, Access MVP