Undefined function 'chopit' in expression

[Modify_inc]

I'm trying to use this function in A2010 but I get this error: Undefined function 'chopit' in expression

I followed the directions and entered my expression as the following: chopit([HomePhone],"h:","o:")
I'm trying to remove the first characters in the HomePhone field, so the phone number only displays (111) 111-1111 instead of h: (111) 1111-1111 or o: (111) 111-1111

Here is the function I found scouring the internet, though I'm not certain how to process it. The way I did it was going under Database Tools, clicking on Visual Basic, right clicking on Modules, selected Insert Module and then pasted the following code. But I'm not sure how to execute it from here or does Access do this automatically after saving the database? I assume that is why my function 'chopit' is undefined.

Also the code mentions I can invoke it from the debug window, but I don't think that applies to standard users.

To use in a query, you'd add (to remove letters "a" & "e"): chopit([YourFieldName],"a","e") AS Expr1

The Code:

Option Compare Database

'Purpose: Remove a list of unwanted
' characters from a string


'Coded by: raskew
'Inputs: From debug window:
' 1) ? chopit("123-45-6789", "-")
' 2) ? chopit(" the quick brown fox ", " ", "o")
'Output: 1) 123456789
' 2) thequickbrwnfx
'*******************************************

Dim strHold As String
Dim i As Integer
Dim n As Integer

strHold = Trim(pstr)
'check for entry
If UBound(varmyvals) < 0 Then Exit Function
For n = 0 To UBound(varmyvals())
Do While InStr(strHold, varmyvals(n)) > 0
i = InStr(strHold, varmyvals(n))
strHold = Left(strHold, i - 1) & Mid(strHold, i + Len(varmyvals(n)))

Loop
Next n
Chopit = Trim(strHold)
End Function

[Modify_inc]

Ok I just noticed that in the Expression Builder, under Functions, then clicking my Database, the chopit function is displayed under Expression Categories, but it's blank under the Expression Values. I'm curious if that signifies anything, or is having the expression values not important here, since I have the values manually typed in already?

Also if the chopit function is displayed in the Expression Categories, can I deduce that the function or code in the module what correctly applied?

[orange]

On looking at your code, I noticed there was no Function statement. So with a little experimenting, I revised the code by adding the Function definition and
then worked through existing code to identify the required input parameters. Here is the revised code. Good luck.

'---------------------------------------------
Function ChopIt(pstr As String, ParamArray VarMyVals() As Variant) As String
'Purpose: Remove a list of unwanted
' characters from a string
'Coded by: raskew
'Modified/revised:19/Aug/2012 --orange
'Inputs: From debug window:
' 1) ? chopit("123-45-6789", "-")
' 2) ? chopit(" the quick brown fox ", " ", "o")
'Output: 1) 123456789
' 2) thequickbrwnfx
'*******************************************

Dim strHold As String
Dim i As Integer
Dim n As Integer

   On Error GoTo ChopIt_Error

strHold = Trim(pstr)
'check for entry
If UBound(VarMyVals) < 0 Then Exit Function
For n = 0 To UBound(VarMyVals())
Do While InStr(strHold, VarMyVals(n)) > 0
i = InStr(strHold, VarMyVals(n))
strHold = Left(strHold, i - 1) & Mid(strHold, i + Len(VarMyVals(n)))

Loop
Next n
ChopIt = Trim(strHold)

   On Error GoTo 0
   Exit Function

ChopIt_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ChopIt of Module AWF_Related"
End Function

[Modify_inc]

On looking at your code, I noticed there was no Function statement. So with a little experimenting, I revised the code by adding the Function definition and
then worked through existing code to identify the required input parameters. Here is the revised code. Good luck.

I copied your code to the module as I described earlier with the steps I used, yet I still get the "Undefined function 'chopit' in expression."

What could I be doing wrong and also it now shows the ChopIt function in the Expression Builder, under Expression Categories and Expression Values.

[orange]

I don't know the details of 2010, but your original post attempted to call a function that was not defined.
Copy the code I gave to a module and save the module. Then, you'll be able to use the function ChopIt.
I'm not sure what you are doing with the expression builder.

You must define the Function before you can use it.

[Modify_inc]

I don't know the details of 2010, but your original post attempted to call a function that was not defined.
Copy the code I gave to a module and save the module. Then, you'll be able to use the function ChopIt.
I'm not sure what you are doing with the expression builder.

You must define the Function before you can use it.

I did copy and paste it to a standard module (not a class module). I clicked the save button, which saves the whole database, didn't see an option to only save the module.

I then closed out of access, and reopened the database, checked to make sure chopit module was still there in VBA. Also confirmed it was displayed on the Navigation Pane as you can see in my two screenshots I provided.

I'm not using the Expression Builder for selecting functions, which of course you can, I open the the builder to give me more room to view my expression and modify it easily. Either way doesn't work and continues to give me the undefined function error.

[orange]

It appears you have module named chopit and the function I provided was chopit.
Rename the module from chopit to ModuleOne

You don't want 2 objects with same name.

[Modify_inc]

It appears you have module named chopit and the function I provided was chopit.
Rename the module from chopit to ModuleOne

You don't want 2 objects with same name.

Wow that worked, what an easy fix! I didn't realize you couldn't have the same name.

My assumptions were to name it all 'chopit' for consistency, never would have dreamed that would have be an issue. I guess that makes sense since modules can have many different procedures in them, and also that both are considered an object.

I found a good article on using modules in VBA just an hour ago, but I haven't came across anything yet relating to this. I wish I had found this article the other day, I could have possibly saved you the additional headache .

Thanks again
Mike

[orange]

No problem, glad to help.