What does 'Undefined Function 'FindReplace' in Expression' mean?

[warrenk]

I created a Class Module which I want to use with a update query, but am receiving a error message. What I did is below. Am I missing anything? This is really new to me so I may be doing everything wrong.

1) Create Class Module

This was taken from the FAQs on this site

'START OF CODE

Option Compare Database

Function FindReplace(strOrig As Variant, strOld As String, strNew As String)
'Function to search and replace characters in a string
'-----------------------------------------------------------
' ARGUEMENT DESCRIPTION
' --------------------------------------------------------
' strOrig String in which to
' search/replace.
'
' strOld String you are searching for.
'
' strNew String to replace the searched
' for string.
' --------------------------------------------------------
' EXAMPLE
' --------------------------------------------------------
' MyString = "555.318.6755"
' MyNewString = FindReplace(MyString,".","-")
' (MyNewString is now "555-318-6755")
'-----------------------------------------------------------

Dim intAt As Integer, strAltered As String

'Check for arguements
If IsNull(strOld) Or IsNull(strNew) Then
FindReplace = "ERROR! CHECK ARGUEMENTS!"
Exit Function
End If

'Check for null string
If IsNull(strOrig) Then
FindReplace = Null
Exit Function
End If

'Do function
For intAt = 1 To Len(strOrig)
If Mid(strOrig, intAt, Len(strOld)) = strOld Then
strAltered = strAltered & strNew
intAt = intAt + (Len(strOld) - 1)
Else
strAltered = strAltered & Mid(strOrig, intAt, 1)
End If
Next intAt
FindReplace = strAltered

End Function
'END OF CODE

2) I created a update query with the following..

FindReplace([Table1]![Address],"-","*")

I get the following message..
'Undefined Function 'FindReplace' in Expression'

I am able to select the function when I build the query. Any suggestions?

Thanks for any help!
Warren

 

[aradha]

Hi Warren,

Why don't you try this way..
in the Update query before you call the FindReplace function

Declare a string variable

Dim str as string
str =

![Address]
FindReplace(str,"-","*")

I hope it works

 

[PHV]

Your UDF (UserDefinedFunction) must be declared as Public in a standard code module (not a form module nor a report one).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244