Avatar of Dumb_Blonde
Dumb_BlondeFlag for United States of America

asked on 

MS Access Public Function returns error "Undefined function in expression

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

Microsoft Access

Avatar of undefined
Last Comment
Rey Obrero (Capricorn1)
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
also do a Debug>compile
correct any error/s raised

and you might as well do a Compact and repair of the db
Avatar of Dumb_Blonde
Dumb_Blonde
Flag of United States of America image

ASKER

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.
check the function that you used in your query, make sure you got it correctly (not mispelled)
Avatar of Dumb_Blonde
Dumb_Blonde
Flag of United States of America image

ASKER

I checked the spelling. I've also tried typing it in the query and selecting/pasting it in the expression builder.
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)

Avatar of Dumb_Blonde
Dumb_Blonde
Flag of United States of America image

ASKER

I'm no longer getting the "Undefined function in expression" error (yay!) but the function isn't returning any results (#Error) ...
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
Avatar of Dumb_Blonde
Dumb_Blonde
Flag of United States of America image

ASKER

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))
   
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Dumb_Blonde
Dumb_Blonde
Flag of United States of America image

ASKER

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.
Yes, pls post another question.
Don't forget to close this one.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo