How To Create Function in Access/VBA

[warrenk]

I need to create a query to find/replace certain characters in a field. From what I can tell, this will require creating a function and then using the function in the query. I found the function I need in the FAQ's on this site (faq705-2384). My question is...how do I go about creating this in my database? I am cunfused as to what is a function, module, class module, etc. Are there any step-by-step guides to help me get to the point where I can create the function and use it in the query?

Thanks for any help!
Warren

 

[FancyPrairie]

A module is a place that contains functions, subs, and classes. A module can exist within a form or report or by itself. To create a function, simply open a new module and enter something like this:

Function MyTest()

    Msgbox "Here I am"

End Function

or you could enter a sub

Sub MyTest()

    Msgbox "Here I am"

End Sub

The difference between a sub and a function is that a function can return a value to the caller. Like this;

Msgbox ReturnValue 3

Function ReturnValue (intValue as Integer) as Integer

    ReturnValue = intValue + 1

End Function

In this case, msgbox will display 4

At the beginning of each module you should have the following statement.

Option Compare Database
Option Explicit

The Option Explicit statement forces you to declare each variable. That way, Access will flag misspellings. To ensure that Access always inserts the phrase "option explicit" in each module you create, within a module, select Tools|Options and then select the Editor tab and make sure the "Required Variable Declaration" is checked.

 

[warrenk]

I think I have gotten a little further..but am still having problems. Here is what I did..

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

 

[PHV]

Your function must be declared as Public in a standard code module.

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

 

[VBslammer]

Delete the class module, then use the built-in Access function:

[tt]Replace([Table1]![Address], "-", "*")[/tt]

The FAQ was probably written before Access 2000.

VBSlammer

"You just have to know which screws to turn." - Professor Bob