undefined funtion in expression

[sandy6078]

Hi I am using code I found seaching this website to convert fields in all CAPS to proper form. The code I am using is from Dennisk

Function proper(strName) As String
' T-SQL by Jens-Michael Lehmann (SQL Server Central)
' Converts McDonalds, O'Brian's, MacPherson's and the Jens-Michael's of the world
' But names followed by 2 or more initials ie brown ag are converted to Brown Ag
' Converted to VBA by Dennis Keeling Sept. 2005
'
Dim intStringLength As Integer
Dim intLoopCounter As Integer
Dim intWordCounter As Integer
Dim intWordStart As Integer
Dim strOutputString As String
Dim strCharAtPos1 As String * 1
Dim strCharAtPos2 As String * 2
Dim strCharAtPos3 As String * 3

If IsNull(strName) Then
Exit Function
End If

'Initialize
strOutputString = ""
intStringLength = Len(strName)
intLoopCounter = 1
intWordStart = 1
intWordCounter = 1

Do While intLoopCounter <= intStringLength
strCharAtPos1 = LCase(Mid(strName, intLoopCounter, 1))
strCharAtPos2 = LCase(Mid(strName, intLoopCounter, 2))
strCharAtPos3 = LCase(Mid(strName, intLoopCounter, 3))

' If at start of word then upper case the character
If intWordStart = 1 Then
strCharAtPos1 = UCase(strCharAtPos1)
intWordStart = 0
intWordCounter = 0
End If

If intWordStart > 1 Then
intWordStart = intWordStart - 1
End If

' If we encounter McSomething, Start a word
If strCharAtPos2 = "Mc" And intWordCounter = 0 Then
intWordStart = 2
End If

' If we encounter MacSomething, Start a word
If strCharAtPos3 = "Mac" And intWordCounter = 0 Then
intWordStart = 3
End If

' If we encounter a hochkomatta (high comma(German))
If strCharAtPos1 = "'" Then
intWordStart = 1
End If

' If we encounter a hyphen
If strCharAtPos1 = "-" Then
intWordStart = 1
End If

' If we encounter a comma
If strCharAtPos1 = "," Then
intWordStart = 1
End If

' If we encounter an underscore
If strCharAtPos1 = "_" Then
intWordStart = 1
End If

' If we encounter a dot
If strCharAtPos1 = "." Then
intWordStart = 1
End If

' If we encounter a space
If strCharAtPos1 = " " Then
intWordStart = 1
End If

' Form the output string
strOutputString = strOutputString & strCharAtPos1
intLoopCounter = intLoopCounter + 1
intWordCounter = intWordCounter + 1

Loop

proper = strOutputString

End Function

However when I replaced the column name in the query with the function I get a "undefined function in expression" error.

I have the following references checked:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
Microsoft Active X data Objects 2.1 Library
Microsoft Office 11.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation


Could someone please help?

 

[RuralGuy]

Did you put this function in a Standard Module? Post how you did the following:

However when I replaced the column name in the query with the function I get a "undefined function in expression" error.

 

[sandy6078]

I created a module called proper then replaced the column name VendorName with pCaseVendor([VendorName]).

 

[KeithG]

You need to make sure your Proper function is a Public function. What is pCaseVendor? From the above code your function name is Proper.

 

[RuralGuy]

You can *not* name the module AND the function the same. Name you module basProper. The column name should be: pCaseVendor:=Proper([VendorName])

 

[RuralGuy]

You need to make sure your Proper function is a Public function...

I'm pretty sure the default in a standard module is Public unless you specify something else.

 

[sandy6078]

I have the function named Public Function proper(strName) As String.

the pCaseVendorroper([VendorName]) - the pCaseVendor is the title of the column.

 

[RuralGuy]

You left out the "=" sign!

pCaseVendor:[B][COLOR="Red"]=[/COLOR][/B]Proper([VendorName])

 

[sandy6078]

Thanks RuralGuy and KeithG, everything is working now.

It's nice to have something go right for once.

 

[RuralGuy]

Glad you got it sorted Sandy.