Microsoft Access 2016 Split Function

[MM1963]

I’m trying to use the split function in Access to split a string into substrings that has a ; delimiter. For some reason “Split” is not being recognized by Access as a valid function. I’m getting an error message stating: Undefine Function “Split” in expression. Anyone else seeing this?

 

[dhookom]

Are you trying to use Split() in a query? I don't believe it works there so you have to create a "wrapper" function that you can call.

Public Function MySplit(strText As String, strFind As String, intSection As Integer) As Variant
    On Error Resume Next
    MySplit = Split(strText, strFind)(intSection)
End Function

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

if standard vba functions stop working this can be a broken reference issue, and not necessarily the reference to vba. Take a look at your references and post the image. Look if any reference shows "MISSING:"

 

[MM1963]

I was trying to use the Split function in a query. Still having trouble using the sample vba. For example. I have a string in a field labeled [qtysold] with a value of "1;23;34;0;2". I need to add the values between the semicolons. So 1+23+34+0+2 to get the answer 60.

 

[dhookom]

You can try a user-defined function like:

Public Function GetSum(strText As String, strDelim As String) As Double
    Dim arNums
    Dim dblOut As Double
    Dim intN As Integer
    arNums = Split(strText, strDelim)
    For intN = 0 To UBound(arNums)
        dblOut = dblOut + arNums(intN)
    Next
    GetSum = dblOut
End Function

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MM1963]

That works for me!. Thank you very muchl

 

[MajP]

I would do it Duane's way, but you could do that in a query without the UDF
Select Eval(Replace([YourField],";","+")) as TheSum,...

 

[dhookom]

Wow MajP. Nice thinking out of the box .

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MM1963]

Thank you for the additional suggestion. That works as well.