Fair warning: I'm not a developer or an engineer. I'm a slightly-more-technical-than-most Business Analyst. My cheif qualifications for the project I'm working on are a subscription to Experts Exchange and being stubborn enough to keep searching until I find a solution here that solves the problem. So please use little words in any explanation. :)
I'm trying to split a long text field using multiple delimiters. I found the perfect solution here on EE posted by Alain Bryden (see below). I copied the code into a module in my DB. The problem I'm having is that when I try to use the function in a query I get the error message "Undefined function in expression". I see the function listed in the expression builder. I have another function (also cribed from here) that works just fine when I select it in the expression builder. I've checked that there are no missing references and changed the module name (originally named the same as the function) to "Utilities". I'm at a loss here and rapidly running up against a deadline. Any help or suggestions you can offer would be much appreciated.
Function SplitMultiDelims(ByRef Text As String, ByRef DelimChars As String, _
Optional ByVal IgnoreConsecutiveDelimiters As Boolean = False, _
Optional ByVal Limit As Long = -1) As String()
Dim ElemStart As Long, N As Long, M As Long, Elements As Long
Dim lDelims As Long, lText As Long
Dim Arr() As String
lText = Len(Text)
lDelims = Len(DelimChars)
If lDelims = 0 Or lText = 0 Or Limit = 1 Then
ReDim Arr(0 To 0)
Arr(0) = Text
SplitMultiDelims = Arr
Exit Function
End If
ReDim Arr(0 To IIf(Limit = -1, lText - 1, Limit))
Elements = 0: ElemStart = 1
For N = 1 To lText
If InStr(DelimChars, Mid(Text, N, 1)) Then
Arr(Elements) = Mid(Text, ElemStart, N - ElemStart)
If IgnoreConsecutiveDelimiters Then
If Len(Arr(Elements)) > 0 Then Elements = Elements + 1
Else
Elements = Elements + 1
End If
ElemStart = N + 1
If Elements + 1 = Limit Then Exit For
End If
Next N
'Get the last token terminated by the end of the string into the array
If ElemStart <= lText Then Arr(Elements) = Mid(Text, ElemStart)
'Since the end of string counts as the terminating delimiter, if the last character
'was also a delimiter, we treat the two as consecutive, and so ignore the last elemnent
If IgnoreConsecutiveDelimiters Then If Len(Arr(Elements)) = 0 Then Elements = Elements - 1
ReDim Preserve Arr(0 To Elements) 'Chop off unused array elements
SplitMultiDelims = Arr
End Function
hope it is not SplitMultiDelims?
if it is, change it to basSplitMultiDelims or modSplitMultiDelims