Spli Command Question

[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 an field in an Access Table. How would you proceed
to reference this item??? (Suppose the Table name is Table1, the field
name is Address)

 

[Fornatian]

I think what you are asking is how can I pass a variable value into this sub procedure? If so, you could...

1. Declare the value inside the procedure like:

Sub StringTestMacro() 
Dim MyString, MyArray, Msg 
MyString = [b]Forms("MyForm").MyControl[/b] or
MyString = [b]DLookup("[CompanyName]", "Shippers", "[ShipperID] = " & Forms!Shippers!ShipperID)[/b]
MyArray = Split(MyString, "X", -1, 1) 
Msg = MyArray(0) 
MsgBox Msg 
Msg = MyArray(1) 
MsgBox Msg 
Msg = MyArray(2) 
MsgBox Msg 
End Sub

or

2. Create an argument in procedure declaration to house a variable you pass in:

Sub StringTestMacro([b]MyString as String[/b]) 
Dim MyArray, Msg 
MyArray = Split(MyString, "X", -1, 1) 
Msg = MyArray(0) 
MsgBox Msg 
Msg = MyArray(1) 
MsgBox Msg 
Msg = MyArray(2) 
MsgBox Msg 
End Sub

then call the function as follows:

StringTestMacro [b]Forms("MyForm").MyControl[/b][code]

 

[Mile-O]

I don't know if the Split() function above is an Access 2000/XP function as it doesn't work in '97 but I've managed to create a similar routine that works with an indefinite amount of words to split.

Sub SplitPhrase(ByVal strSplit As String)

    Dim strSplits() As String
    Dim intCounter As Integer, intTotal As Integer, intPosition As Integer
    intPosition = 1

    strSplit = Trim(strSplit) & " "
    
    For intCounter = 1 To Len(strSplit)
        If Mid(strSplit, intCounter, 1) = " " Then
            ReDim Preserve strSplits(intTotal)
            strSplits(intTotal) = Trim(Mid(strSplit, intPosition, intCounter - 1))
            intPosition = intCounter + 1
            intTotal = intTotal + 1
        End If
    Next intCounter
    
    For intCounter = 0 To intTotal - 1
        MsgBox strSplits(intCounter)
    Next intCounter

End Sub

This one splits a phrase up by spaces although each reference to " " can be changed to whatever character you wish.