If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Split function in a query.
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 |
#2
|
|||
|
|||
Using Split function in a query.
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 |
#3
|
|||
|
|||
Using Split function in a query.
Thanks for the response. I get is "#Error" on every record.
"Deville" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|