access vba office 365 undefined function error.

I have a new db in 2003 to 2007 format i am using access 365. I have a function that I hoped would split the string and return the rows of the following field:

SplitMultiDelims([VIN Number / Model Prefix],";")

AB1111; AB1112; AB1114

I would want returned three rows
AB1111
AB1112
AB1114

everytime i try to run it says undefined function but not sure whats missing.

references attached
The code I found via google.

I created a hello world function and put that in the module and that worked so not sure what item is missing.


Function SplitMultiDelims(Text As String, DelimChars As String) As String()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SplitMutliChar
' This function splits Text into an array of substrings, each substring
' delimited by any character in DelimChars. Only a single character
' may be a delimiter between two substrings, but DelimChars may
' contain any number of delimiter characters. If you need multiple
' character delimiters, use the SplitMultiDelimsEX function. It returns
' an unallocated array it Text is empty, a single element array
' containing all of text if DelimChars is empty, or a 1 or greater
' element array if the Text is successfully split into substrings.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos1 As Long
Dim N As Long
Dim M As Long
Dim Arr() As String
Dim I As Long

''''''''''''''''''''''''''''''''
' if Text is empty, get out
''''''''''''''''''''''''''''''''
If Len(Text) = 0 Then
    Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''
' if DelimChars is empty, return original text
'''''''''''''''''''''''''''''''''''''''''''''
If DelimChars = vbNullString Then
    SplitMultiDelims = Array(Text)
    Exit Function
End If

'''''''''''''''''''''''''''''''''''''''''''''''
' oversize the array, we'll shrink it later so
' we don't need to use Redim Preserve
'''''''''''''''''''''''''''''''''''''''''''''''
ReDim Arr(1 To Len(Text))

I = 0
N = 0
Pos1 = 1

For N = 1 To Len(Text)
    For M = 1 To Len(DelimChars)
        If StrComp(Mid(Text, N, 1), Mid(DelimChars, M, 1), vbTextCompare) = 0 Then
            I = I + 1
            Arr(I) = Mid(Text, Pos1, N - Pos1)
            Pos1 = N + 1
            N = N + 1
        End If
    Next M
Next N

If Pos1 <= Len(Text) Then
    I = I + 1
    Arr(I) = Mid(Text, Pos1)
End If

''''''''''''''''''''''''''''''''''''''
' chop off unused array elements
''''''''''''''''''''''''''''''''''''''
ReDim Preserve Arr(1 To I)
SplitMultiDelims = Arr
    
End Function

Open in new window

PeterBaileyUkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You're trying to call a Sub from a query. Subs do not return anything, so it will fail. I did this:

Function SplitInfo(vText As String, DelimChar As String)
    Dim vArr() As String, j As Long
    If vText & "" = "" Then Exit Function

    vArr = Split(vText, DelimChar)
   
    SplitInfo = vArr(0)
   
End Function
And changed the query to this:

Expr1: SplitInfo([VIN Number / Model Prefix],";")

That parses the FIRST value (before the ;  delimiter) and returns it to the query.

If you have "AB1111; AB1112; AB1114" and pass that in, however, it would just return the first item in that string. I'm not sure how you'd return 3 distinct Rows in your query for a value like that - in fact, I don't think you can do this. I think you'd need a subquery that could be joined to the main query, which would show 3 exactly duplicated rows EXCEPT for that value.
0
 
PeterBaileyUkAuthor Commented:
and reference attached
ee.PNG
0
 
Rey Obrero (Capricorn1)Commented:
if you are just after splitting a simple string AB1111; AB1112; AB1114

I would want returned three rows
AB1111
AB1112
AB1114

you just need this simple code

Sub SplitInfo(vText As String, DelimChar As String)
Dim vArr() As String, j As Long
If vText & "" = "" Then Exit Sub

vArr = Split(vText, DelimChar)
For j = 0 To UBound(vArr)
    Debug.Print vArr(j)
Next
End Sub

Open in new window

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are those references as shown in Office 365? Or are those from your dev machine?

In most cases, you don't need the extensibility reference unless you have code that works with the VBA Editor environment (i.e. opening a Module and changing code there), so try removing that.

You should also determine if you need the other references. To do that, first, make a backup, then do this:

Open the project, open the VBA Editor. Click Debug - Compile and fix any errors you encounter. After doing that, remove a single reference, and then click Debug - Compile again. If you do NOT receive any errors or warnings, then you don't need that reference and can remove it.

Note that you'll need (at least) the top 3 items on your reference list.
0
 
Patrick MatthewsCommented:
You did not say *how* you are using that function.  Is it in a query?  As a data source in a form or report?  Are you using it in another VBA procedure or an Access macro?  Please fill us in on exactly when, where, and how you are using it.

Also, make sure that the function is in a "regular" VBA module--not a form, report, or class module--and that the module name is not the same as the function name.
0
 
PeterBaileyUkAuthor Commented:
I followed your tips but even with a simple sub it is just not working as its small I have zipped and attached it so maybe someone could shed light on this
Database1.zip
0
 
PeterBaileyUkAuthor Commented:
ok I got it now, I created a recordset looped through and for each iteration created an array with the split function and added that to a duplicate table without data in it.

Public Sub Main()
Dim dbs As DAO.Database
Dim rstVins As DAO.Recordset
Dim rstNew As DAO.Recordset
Set dbs = CurrentDb
Set rstNew = dbs.OpenRecordset("NewTable")
Set rstVins = dbs.OpenRecordset("QryData")
Dim RecordCountIndex As Long
Dim TestArray() As String
Dim TestString As String
Dim x As Long
Dim LastNonEmpty As Integer
Dim i As Integer

With rstVins
.MoveLast
x = .RecordCount
Debug.Print "total records: " & x
    .MoveFirst
  'method one
    Do Until .EOF
 TestArray = Split(.Fields("VINstem").Value)

        For i = 0 To UBound(TestArray)
          TestArray(i) = Replace(TestArray(i), ";", "")
          rstNew.AddNew
          rstNew!Vinstem = TestArray(i)
          rstNew!SuzukiSparePartsCatalog = rstVins.Fields("SuzukiSparePartsCatalog").Value
          rstNew!Modelname = rstVins.Fields("Modelname").Value
          rstNew!YearCode = rstVins.Fields("YearCode").Value
          rstNew!Year = rstVins.Fields("Year").Value
          rstNew.Update
          
          
        Next i
 
    rstVins.MoveNext

    Loop
End With
MsgBox ("end")
End Sub

Open in new window

0
 
PeterBaileyUkAuthor Commented:
thank you..steered me in the right direction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.