SQL in VBA for Access

[Guest]

I have copied VB function code from the internet. I need the return value of
this function to be part of the SELECT statement in a SQL statement. But,
I'm not sure about the proper syntax. That is my question. For those of you
familiar with SOUNDEX that's what the function is called. This is what I
need my SQL statement to look like:

strSQL = "INSERT INTO [Number] ( Occupation ) " & _
"SELECT Table1.occupation " & _ '<----- here is where I need "SOUNDEX(
occupation )"
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
CurrentDb.Execute strSQL, dbFailOnError

So, do I put the call for the function inside the double-quotes? Do I tack
it onto the end with an "&" after the "SELECT Table1.occupation "? Help is
greatly appreciated!

 

[dbahooker]

if you were using Access Data Projects; you could dynamically build
queries on the db side.

With Access MDB you're pretty much stuck with a jerry-rigged solution
where you'd be better off keeping it outside of a query; and just using
dynamic SQL Statements.

MDB is obsolete.

Spit on people that use it.

-Aaron

 

[Douglas J. Steele]

Your SQL is saying you have a table named Number with a single field
Occupation in it.

If you're trying to insert the value of the SoundEx function into that
Occupation field, you'd use:

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

 

[Guest]

Alright, but its giving me the error: "Undefined Function", even though I
have created the Soundex function directly below the sub. My code looks like
this starting at the SQL statement:

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT Soundex(Table1.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

Your SQL is saying you have a table named Number with a single field
Occupation in it.

If you're trying to insert the value of the SoundEx function into that
Occupation field, you'd use:

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



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have copied VB function code from the internet. I need the return value
of
this function to be part of the SELECT statement in a SQL statement. But,
I'm not sure about the proper syntax. That is my question. For those of
you
familiar with SOUNDEX that's what the function is called. This is what I
need my SQL statement to look like:

strSQL = "INSERT INTO [Number] ( Occupation ) " & _
"SELECT Table1.occupation " & _ '<----- here is where I need "SOUNDEX(
occupation )"
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
CurrentDb.Execute strSQL, dbFailOnError

So, do I put the call for the function inside the double-quotes? Do I
tack
it onto the end with an "&" after the "SELECT Table1.occupation "? Help
is
greatly appreciated!

 

[Douglas J. Steele]

I always get this mixed up, but someone will doubtless correct me if I'm
wrong... <g>

There's a difference between how SQL gets executed using DoCmd.RunSQL and
database.Execute: one is aware of VBA functions, while the other one isn't.

See whether it works this way:

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT Soundex(Table1.occupation) " & _
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

If not, where is the Soundex function defined? It must be in a Module, not a
Class or the code associated with a form or report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Alright, but its giving me the error: "Undefined Function", even though I
have created the Soundex function directly below the sub. My code looks
like
this starting at the SQL statement:

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT Soundex(Table1.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

Your SQL is saying you have a table named Number with a single field
Occupation in it.

If you're trying to insert the value of the SoundEx function into that
Occupation field, you'd use:

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



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have copied VB function code from the internet. I need the return
value
of
this function to be part of the SELECT statement in a SQL statement.
But,
I'm not sure about the proper syntax. That is my question. For those
of
you
familiar with SOUNDEX that's what the function is called. This is what
I
need my SQL statement to look like:

strSQL = "INSERT INTO [Number] ( Occupation ) " & _
"SELECT Table1.occupation " & _ '<----- here is where I need
"SOUNDEX(
occupation )"
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
CurrentDb.Execute strSQL, dbFailOnError

So, do I put the call for the function inside the double-quotes? Do I
tack
it onto the end with an "&" after the "SELECT Table1.occupation "?
Help
is
greatly appreciated!

 

[Dirk Goldgar]

I always get this mixed up, but someone will doubtless correct me if
I'm wrong... <g>

Well ...

There's a difference between how SQL gets executed using DoCmd.RunSQL
and database.Execute: one is aware of VBA functions, while the other
one isn't.

If executed within Access, both database.Execute and DoCmd.RunSQL are
aware of user-defined functions. It's only when queries are executed
from outside Access that user-defined functions are unrecognized.

If not, where is the Soundex function defined? It must be in a
Module, not a Class or the code associated with a form or report.

I believe that's the problem. From appearances, the Soundex() function
is defined in the form's module.

 

[Smartin]

Alright, but its giving me the error: "Undefined Function", even though I
have created the Soundex function directly below the sub. My code looks like
this starting at the SQL statement:

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT Soundex(Table1.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

Interesting Soundex function! Was this your idea? I guess I always
assumed it was going to be more complicated than that... You don't mind
if I borrow it do you?

I suppose you've read the other replies, but just in case: Put this
function in a standard module (and declare it as Public), not in the
form's code.

 

[Douglas J. Steele]

Mark1 wrote:

Interesting Soundex function! Was this your idea? I guess I always assumed
it was going to be more complicated than that... You don't mind if I
borrow it do you?

Actually, it's supposed to be just slightly more complicated than that: it's
missing the rule that if H or W separates 2 consonants that have the same
soundex code, the consonant to the right of the vowel is not coded (for
instance, in Ashcraft, the S is ignored because S and C both have the same
Soundex value - 2 - and the H is between them.) Of course, some
implementations of Soundex ignore that rule.

For more about Soundex and another similar technique Levenshtein Distance,
see my April 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Smartin]

Actually, it's supposed to be just slightly more complicated than that: it's
missing the rule that if H or W separates 2 consonants that have the same
soundex code, the consonant to the right of the vowel is not coded (for
instance, in Ashcraft, the S is ignored because S and C both have the same
Soundex value - 2 - and the H is between them.) Of course, some
implementations of Soundex ignore that rule.

For more about Soundex and another similar technique Levenshtein Distance,
see my April 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Great stuff, Douglas. Thanks again!

 

[Tony Toews]

"SELECT Soundex(Table1.occupation) " & _

Try the following

"SELECT " & Soundex(Table1.occupation) & " " & _

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[dbahooker]

great well maybe if you weren't a MDB pussy maybe we would give a shit.

go play with your barbies; wuss

-Aaron
ADP Nationalist