Using Split function in a query.

[Randal]

Warning - I am a complete novice to VB. However, I have a situation where
the Split function is really needed in a query. I copied the following from
the Microsoft website and pasted it into a module.
#1) I have no idea how to call it from my query.
#2) Running it from the immediate window gives me the error "Compile error:
Expected: =.

Any help is appreciated.

Public Function Split(ByVal InputText As String, _
Optional ByVal Delimiter As String) As Variant

' This function splits the sentence in InputText into
' words and returns a string array of the words. Each
' element of the array contains one word.

' This constant contains punctuation and characters
' that should be filtered from the input string.
Const CHARS = ".!?,;:""'()[]{}"
Dim strReplacedText As String
Dim intIndex As Integer

' Replace tab characters with space characters.
strReplacedText = Trim(Replace(InputText, _
vbTab, " "))

' Filter all specified characters from the string.
For intIndex = 1 To Len(CHARS)
strReplacedText = Trim(Replace(strReplacedText, _
Mid(CHARS, intIndex, 1), " "))
Next intIndex

' Loop until all consecutive space characters are
' replaced by a single space character.
Do While InStr(strReplacedText, " ")
strReplacedText = Replace(strReplacedText, _
" ", " ")
Loop

' Split the sentence into an array of words and return
' the array. If a delimiter is specified, use it.
'MsgBox "String:" & strReplacedText
If Len(Delimiter) = 0 Then
Split = VBA.Split(strReplacedText)
Else
Split = VBA.Split(strReplacedText, Delimiter)
End If
End Function

 

[Deville]

Try using this function. In order to use the function in a
query type Split([FieldName],"What Ever Character you
wanna Split at")

If the Character you wanna Split at is a semicolon it
would read:

Split([Field1],";")...

Have fun...


Public Function Split(ByVal InputText As String, _
Optional ByVal Delimiter As String) As Variant

' This function splits the sentence in InputText into
' words and returns a string array of the words. Each
' element of the array contains one word.

' This constant contains punctuation and characters
' that should be filtered from the input string.
Const CHARS = ".!?,;:""'()[]{}"
Dim strReplacedText As String
Dim intIndex As Integer

' Replace tab characters with space characters.
strReplacedText = Trim(Replace(InputText, _
vbTab, " "))

' Filter all specified characters from the string.
For intIndex = 1 To Len(CHARS)
strReplacedText = Trim(Replace(strReplacedText, _
Mid(CHARS, intIndex, 1), " "))
Next intIndex

' Loop until all consecutive space characters are
' replaced by a single space character.
Do While InStr(strReplacedText, " ")
strReplacedText = Replace(strReplacedText, _
" ", " ")
Loop

Split = strReplacedText

End Function

 

[Randal]

Thanks for the response. I get is "#Error" on every record.