Refrencing a Field in the Split Command

[rjusa]

The following routine will take a string _expression and break it into
parts as set by the delimeter:

Sub StringTestMacro()
Dim MyString, MyArray, Msg
MyString = "VBScriptXisXfun!"
MyArray = Split(MyString, "X", -1, 1)
Msg = MyArray(0)
MsgBox Msg
Msg = MyArray(1)
MsgBox Msg
Msg = MyArray(2)
MsgBox Msg
End Sub

This works and its kind of neat. It returns

VBScrip
is
fun!

But, what if instead you want to replace the line

MyString = "VBScriptXisXfun!"

with a reference to a field in an Access Table or Form. How would you proceed
to reference this item??? (Suppose the Table or Form name is Table1, the field
name is Address). Should I reference the Table or Form? Conceptually I know I need to change Address to a string but I'm either brain dead, stupid or all of the above!

 

[rjusa]

Pat-

You're a genius!

I coded the following:

Sub Macro69()
Dim Mystring As String, MyArray, Msg
DoCmd.OpenForm "String"
Mystring = Forms!String!Address
MyArray = Split(Mystring, "*", -1, 1)
Msg = MyArray(0)
MsgBox Msg
Msg = MyArray(1)
MsgBox Msg
End Sub

Returned excatly what I needed. Thank you! Next, my question is:

I knew apriori how to set the MyArray parameters (i.e., 0, 1)...since I don't have this luxury in the real world, how can I incorporate code to count the number of times the "*" character occurs and then alter the existing code to tell MyArray to loop(?) 1 to n times?