User defined function in Access query??

[esteiner]

I have been trying to solve performance problems with some of my VBA code. Several members have suggested using the following user defined public function within an Access query. I did create a separate module for this function:

Public Function NumPart(fld As String) As String
Dim n As Integer
For n = 1 To Len(fld)
If IsNumeric(Mid(fld, n, 1)) Then
NumPart = Right(fld, Len(fld) - n + 1)
Exit For
End If
Next
End Function

Here is the form code and query.....

Private Sub cmdSplitMPN_Click()
On Error GoTo E_Handle
Dim strTableName As String
Dim strSQL As String
Set db = DAO.DBEngine(0)(0)

strTableName = "pull_inv_BRE"

strSQL = "UPDATE [" & strTableName & "] " & _
"SET [CorePartNumber] = NumPart(manfPartNum) " & _
"Where manfPartNum LIKE '*[0-9]*' "

db.Execute strSQL

sExit:
On Error Resume Next
Reset
Exit Sub
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit

End Sub

I receive the Error: 3085 Undefined funtion "NumPart" in expression. My research suggests that Access will not allow a user defined function to be used within the query. Is this true? If this is truly a limitation of Access, can anyone suggest an alternative?

Thank you in advance for you help. I am banging my head against the wall here.

 

[dhookom]

Did you make sure the name of your module is not the same as the name of the function?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[esteiner]

That fixed the problem! Thank you very much. I guess it makes sense that two objects cannot share the same name.