MS Access Public Function returns error "Undefined function in expression

Dumb_Blonde
Dumb_Blonde used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
what name did you give to the module where the function is?

hope it is not SplitMultiDelims?
if it is, change it to basSplitMultiDelims or modSplitMultiDelims
Top Expert 2016

Commented:
also do a Debug>compile
correct any error/s raised

and you might as well do a Compact and repair of the db

Author

Commented:
I originally had named it SplitMultiDelims but after reading a bit here changed it to "basUtilities". I should have mentioned that I did do a Debug/compile without it raising any errors. I've compiled it again and done a compact and repair and am still having the same issue ...

Thanks.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
check the function that you used in your query, make sure you got it correctly (not mispelled)

Author

Commented:
I checked the spelling. I've also tried typing it in the query and selecting/pasting it in the expression builder.
Top Expert 2016

Commented:
change this


Function SplitMultiDelims(ByRef Text As String, ByRef DelimChars As String, _
        Optional ByVal IgnoreConsecutiveDelimiters As Boolean = False, _
        Optional ByVal Limit As Long = -1) As String()

with


Function SplitMultiDelims(ByRef Text As String, ByRef DelimChars As String, _
        Optional ByVal IgnoreConsecutiveDelimiters As Boolean = False, _
        Optional ByVal Limit As Long = -1)

Author

Commented:
I'm no longer getting the "Undefined function in expression" error (yay!) but the function isn't returning any results (#Error) ...
Top Expert 2016

Commented:
use this one


Function SplitMultiDelims(ByRef Text As String, ByRef DelimChars As String, _
        Optional ByVal IgnoreConsecutiveDelimiters As Boolean = False, _
        Optional ByVal Limit As Long = -1) As String

Author

Commented:
Compile error (type mismatch) on:

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))
   
Top Expert 2016
Commented:
use

SplitMultiDelims = Arr(0)

Author

Commented:
That solved my original problem. Thank you!

The function executes and returns the string before the first delimiter. Based on what I had originally read I expected it to return all the delimited values. Was I wrong about that? Should I just submit another question regarding my expectations of the outcome versus the actual outcome?

This is the first question I've ever submitted so I'm not sure of the parameters ...

Thanks again.
Top Expert 2016

Commented:
Yes, pls post another question.
Don't forget to close this one.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial