Call a UDF from SQL

[Guest]

How can I call a user defined function from a SQL statement? I need
something like this in my VB code:

strSQL = "SELECT NAME, udf(ADDRESS) " & _
"FROM TABLE1" & _
"WHERE whatever = whatever"
blah, blah, blah.

The udf is my function and ADDRESS from my table is what I'm trying to pass
to it. If I take the udf(ADDRESS) out of the double-quotes, VBA seems to
recognize it, but I need it to really be part of the result of my SQL
statement. Thank you so much!

 

[Dirk Goldgar]

How can I call a user defined function from a SQL statement? I need
something like this in my VB code:

strSQL = "SELECT NAME, udf(ADDRESS) " & _
"FROM TABLE1" & _
"WHERE whatever = whatever"
blah, blah, blah.

The udf is my function and ADDRESS from my table is what I'm trying
to pass to it. If I take the udf(ADDRESS) out of the double-quotes,
VBA seems to recognize it, but I need it to really be part of the
result of my SQL statement. Thank you so much!

I take it you tried it as above and the function wasn't recognized. How
did you execute your statement? Did you run it from within Access, or
in some other environment? If you run it within Access, it should have
no problem understanding and resolving your function reference. If you
run it outside of Access, from a web page for example, it's not going to
know about any UDFs in the database.

 

[Guest]

I'm running the SQL statement from a Command Button on a form. When I ask to
debug the program it highlights the execution code:
CurrentDb.Execute strSQL, dbFailOnError

My whole code is like this starting at the SQL statement:

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT soundex(occupation)" & _
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
CurrentDb.Execute strSQL, dbFailOnError

End Sub

Function Soundex(ByVal S As String) As String
S = UCase$(Trim$(S))
Dim Code As Integer: Code = 0
Dim Last As Integer: Last = 0
Dim R As String: R = ""
Dim i As Long: For i = 1 To Len(S)
Select Case Mid$(S, i, 1)
Case "B", "F", "P", "V"
Code = 1
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
Code = 2
Case "D", "T"
Code = 3
Case "L"
Code = 4
Case "M", "N"
Code = 5
Case "R"
Code = 6
Case Else
Code = 0
End Select
If (i = 1) Then
R = Mid$(S, 1, 1)
ElseIf (Code <> 0 And Code <> Last) Then
R = R & Code
End If
Last = Code
Next i
Soundex = Mid$(R & "0000", 1, 4)
End Function

 

[Dirk Goldgar]

I'm running the SQL statement from a Command Button on a form. When
I ask to debug the program it highlights the execution code:
CurrentDb.Execute strSQL, dbFailOnError

My whole code is like this starting at the SQL statement:

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT soundex(occupation)" & _
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
CurrentDb.Execute strSQL, dbFailOnError

End Sub

Function Soundex(ByVal S As String) As String
S = UCase$(Trim$(S))
Dim Code As Integer: Code = 0
Dim Last As Integer: Last = 0
Dim R As String: R = ""
Dim i As Long: For i = 1 To Len(S)
Select Case Mid$(S, i, 1)
Case "B", "F", "P", "V"
Code = 1
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
Code = 2
Case "D", "T"
Code = 3
Case "L"
Code = 4
Case "M", "N"
Code = 5
Case "R"
Code = 6
Case Else
Code = 0
End Select
If (i = 1) Then
R = Mid$(S, 1, 1)
ElseIf (Code <> 0 And Code <> Last) Then
R = R & Code
End If
Last = Code
Next i
Soundex = Mid$(R & "0000", 1, 4)
End Function

What error message (and error number, if you have it) are you getting?
The code works fine for me.

 

[Dirk Goldgar]

I'm running the SQL statement from a Command Button on a form. When
I ask to debug the program it highlights the execution code:
CurrentDb.Execute strSQL, dbFailOnError

My whole code is like this starting at the SQL statement:

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT soundex(occupation)" & _
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
CurrentDb.Execute strSQL, dbFailOnError

End Sub

Function Soundex(ByVal S As String) As String

Oh, wait -- I see the problem now! Your Soundex() function is defined
in the form's module. That makes it local to the form, and only code
run within the form's module will recognize it. Your query, though
requested from inside the form, is executed *outside* the form, by the
query engine, and so knows nothing about the function.

You must define the function in a standard module, not in the form's
module, for it to be recognized.

 

[Guest]

Dirk, you are my hero!!!!!!!!!!

I'm running the SQL statement from a Command Button on a form. When
I ask to debug the program it highlights the execution code:
CurrentDb.Execute strSQL, dbFailOnError

My whole code is like this starting at the SQL statement:

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT soundex(occupation)" & _
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
CurrentDb.Execute strSQL, dbFailOnError

End Sub

Function Soundex(ByVal S As String) As String

Oh, wait -- I see the problem now! Your Soundex() function is defined
in the form's module. That makes it local to the form, and only code
run within the form's module will recognize it. Your query, though
requested from inside the form, is executed *outside* the form, by the
query engine, and so knows nothing about the function.

You must define the function in a standard module, not in the form's
module, for it to be recognized.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)