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!

"Mark1" <(E-Mail Removed)> wrote in message
news:C5BDC08B-2679-4C99-B0A1-(E-Mail Removed)
> 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.

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

(please reply to the newsgroup)

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" wrote:

> "Mark1" <(E-Mail Removed)> wrote in message
> news:C5BDC08B-2679-4C99-B0A1-(E-Mail Removed)
> > 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

"Mark1" <(E-Mail Removed)> wrote in message
news:CF2D9E3B-6049-432D-844D-(E-Mail Removed)
> 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, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Mark1" <(E-Mail Removed)> wrote in message
news:CF2D9E3B-6049-432D-844D-(E-Mail Removed)
> 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)

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

"Dirk Goldgar" wrote:

> "Mark1" <(E-Mail Removed)> wrote in message
> news:CF2D9E3B-6049-432D-844D-(E-Mail Removed)
> > 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)
>
>
>