Variant Array Split() Syntax

[cheuschober]

Hey there folks. Run into a pickle that I just can't seem to find documented in what I have available to me (which is mostly O'Reilly or MsVBA Help).

Does anyone know the proper syntax for getting a comma-delimited text field (pulled via recordset) into a dynamic variant array using split()? I've tried quite a few combos to create it but no luck thus far (continue receiving a 'type mismatch' error).

Code is as follows (lets assume I know how to open a recordset and use it properly -- recordset does retrieve the value of the requested text field with no hangups):

Dim varSendTo() as Variant
Dim strSendTo as String

'Creates a string SendTo just to be redundant (shouldn't need this)
strSendTo = rs!SendTo

'Splits "To" Array
varSendTo = Split(strSendTo, ",", -1)

Could it be that I'm missing some reference ? I've tried various combinations of varSendTo and varSendTo(), strSendTo and rs!SendTo among others with little luck in dealing with this mismatch.

Any thoughts? Access 2k here with Jet 8 and the most recent SP so I'm reasonably sure split should be supported.

Many thanks,
~Chad

 

[pono1]

Chad,

Something to try...

Dim varSendTo() as String 'changed to string
Dim strSendTo as String
Dim i as long

'Creates a string SendTo just to be redundant (shouldn't need this)
strSendTo = rs!SendTo

'Test
    debug.print strSendTo

'Splits "To" Array
varSendTo = Split(strSendTo, ",")

'Check it
    For i = LBound(varSendTo) To UBound(varSendTo)
        Debug.Print varSendTo(i)
    Next i

Regards,
Tim

 

[cheuschober]

Many thanks, Tim...

I actually left out one combination that works (though you wouldn't think it would). The VBA Help File states that you have to pass the results of Split() to a dynamic array (I needed a variant array for my purposes). Hence why I thought:

Dim varSendTo() as Variant

In actuality, it seems, the Split() function changes the variable it passes its result to into an array as a part of the function so the correct syntax would be...

Dim varSendTo as Variant
varSendTo = Split(rs!SendTo,",",-1)

This gives me the resultant variant array I need.

Regards,
~Chad