SQL Undefined Function Error

[ApexData@gmail.com]

Private Sub Command35_Click ()
MsgBox (BestSoundex2([LNAME], 6))
DoCmd.RunSQL "UPDATE [T-PERS] SET [SX2] = (BestSoundex2([LNAME],
6))"
End Sub

The Msgbox displays just fine, but the SQL statement gives the
following error:
Undefined Function 'BestSoundex2' In expression

I'm looking to fill my existing table's SX2 field with a soundex
functions values.
I used this once before and it worked fine. The msgbox works.

Can anyone see whats wrong here???

['69 Camaro]

Re: SQL Undefined Function Error

Hi.

Can anyone see whats wrong here???

Does your VBA code compile without errors (without any missing References)?
Is the BestSoundex2( ) procedure a public function, and is it defined in a
standard module?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


<ApexData@gmail .comwrote in message
news:1169195127 .032257.156540@ q2g2000cwa.goog legroups.com...

Private Sub Command35_Click ()
MsgBox (BestSoundex2([LNAME], 6))
DoCmd.RunSQL "UPDATE [T-PERS] SET [SX2] = (BestSoundex2([LNAME],
6))"
End Sub
>
The Msgbox displays just fine, but the SQL statement gives the
following error:
Undefined Function 'BestSoundex2' In expression
>
I'm looking to fill my existing table's SX2 field with a soundex
functions values.
I used this once before and it worked fine. The msgbox works.
>
Can anyone see whats wrong here???
>

[ApexData@gmail.com]

Re: SQL Undefined Function Error

Thanks for your response HTH.

Does your VBA code compile without errors (without any missing References)?

Yes

Is the BestSoundex2( ) procedure a public function

Yes and I tried private also since you mentioned it.

and is it defined in a standard module?

Yes, and exists in an isolated form for test reasons and the form is
bound to
1-table. And tried it bound and unbound. And as I mentioned, it works
fine in the msg box.

Greg

[ApexData@gmail.com]

Re: SQL Undefined Function Error

I got it to work by placing the Function in a Module.
Makes no sense to me? Any thoughts?

Greg

['69 Camaro]

Re: SQL Undefined Function Error

Hi.

Thanks for your response HTH.

"HTH" is a common abbreviation in the newsgroups for "hope this helps." ;-)

>Is the BestSoundex2( ) procedure a public function

Yes and I tried private also since you mentioned it.

It _must_ be a public function for Jet to "find" it and use it in the Jet
Expression Service.

>and is it defined in a standard module?

Yes

Something is wrong then. Are you sure you've spelled it correctly? I find
"copy/paste" is the only _almost_ guaranteed way for me to get the procedure
name correct every time when I use it elsewhere in the application.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


<ApexData@gmail .comwrote in message
news:1169223303 .774036.314290@ m58g2000cwm.goo glegroups.com.. .

Thanks for your response HTH.
>

>Does your VBA code compile without errors (without any missing
>References)?

Yes
>

>Is the BestSoundex2( ) procedure a public function

Yes and I tried private also since you mentioned it.
>

>and is it defined in a standard module?

Yes, and exists in an isolated form for test reasons and the form is
bound to
1-table. And tried it bound and unbound. And as I mentioned, it works
fine in the msg box.
>
Greg
>

['69 Camaro]

Re: SQL Undefined Function Error

Hi, Greg.

<from elsethread:>

>and is it defined in a standard module?

Yes

.. . .

I got it to work by placing the Function in a Module.

Excellent! That's the standard module I was talking about. All the other
modules in the database application are class modules, and unless an object
of that class is instantiated, Jet can't use anything defined in that class
(such as procedures or properties) in the Jet Expression Service.
Unfortunately, one cannot instantiate an object within Jet SQL. That can
only be done within a VBA procedure, so that's why the public function must
be defined in a standard module, where it's "visible" to Jet.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


<ApexData@gmail .comwrote in message
news:1169224409 .593984.188500@ 38g2000cwa.goog legroups.com...

>I got it to work by placing the Function in a Module.
Makes no sense to me? Any thoughts?
>
Greg
>

[ApexData@gmail.com]

Re: SQL Undefined Function Error

Excellent! That's the standard module I was talking about. All the other
modules in the database application are class modules, and unless an object
of that class is instantiated, Jet can't use anything defined in that class
(such as procedures or properties) in the Jet Expression Service.
Unfortunately, one cannot instantiate an object within Jet SQL. That can
only be done within a VBA procedure, so that's why the public function must
be defined in a standard module, where it's "visible" to Jet.

Originally, I placed the function in a "FormModule " which I think is a
class module?
This was when it would not work with SQL. Or, Jet is guess. (Private
or Public no work)
But as I mentioned, it worked in a Msgbox against 1-Record.

When placed in an "Module Object" Public module or as you mentioned
Standard Module
it worked. Thankyou for the advice. I need to understand modules
better. And, I'm not totally sure I understand what "instantiat e"
means.

ThanksAgain "Gunny"
Greg

['69 Camaro]

Re: SQL Undefined Function Error

Hi, Greg.

Originally, I placed the function in a "FormModule " which I think is a
class module?

Yes.

But as I mentioned, it worked in a Msgbox against 1-Record.

Inside a VBA procedure, yes. Within the Jet Expression Service that Jet SQL
uses to interpret the user-defined function, no. You have to consider the
context of where the VBA code is being used. It's like if you traveled to a
foreign country where the native tongue isn't English, but you expect to be
able to communicate only in "your" language. When the language isn't the
same, you need to use an interpreter to communicate. That's what Jet is
doing with the Jet Expression Service. This service interprets the VBA code
and returns a value that Jet can then operate on. But the Jet Expression
Service has some limitations, such as only being able to interpret public
functions, not public subroutines, because subroutines don't return a value,
but functions do.

And, I'm not totally sure I understand what "instantiat e"
means.

It's programmer-speak used in object-oriented programming. It means that a
programmer has used code to define an object which has properties (things
that describe the object, such as size, color, et cetera) and methods
(things the object can do, such as open a file, change a value in a column,
et cetera). The complete code definition of an object is called a class.

When the class is needed to do something in the program (such as manipulate
files on the hard drive or display something on your monitor), the
programmer uses code to place an "instance" of that object in memory, so
that the computer has a physical memory address to refer to in order to read
and write the properties of that object and execute the methods of that
object. This "instance" of the object (and there can be many other
instances running at the same time) is called instantiation of the object as
soon as it's placed into memory. The object "instance" is placed into
memory so that the computer can use the object's code to carry out whatever
the programmer requires the object to do.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


<ApexData@gmail .comwrote in message
news:1169230318 .066848.124910@ l53g2000cwa.goo glegroups.com.. .

>

>Excellent! That's the standard module I was talking about. All the
>other
>modules in the database application are class modules, and unless an
>object
>of that class is instantiated, Jet can't use anything defined in that
>class
>(such as procedures or properties) in the Jet Expression Service.
>Unfortunatel y, one cannot instantiate an object within Jet SQL. That can
>only be done within a VBA procedure, so that's why the public function
>must
>be defined in a standard module, where it's "visible" to Jet.

>
Originally, I placed the function in a "FormModule " which I think is a
class module?
This was when it would not work with SQL. Or, Jet is guess. (Private
or Public no work)
But as I mentioned, it worked in a Msgbox against 1-Record.
>
When placed in an "Module Object" Public module or as you mentioned
Standard Module
it worked. Thankyou for the advice. I need to understand modules
better. And, I'm not totally sure I understand what "instantiat e"
means.
>
ThanksAgain "Gunny"
Greg
>

['69 Camaro]

Re: SQL Undefined Function Error

Hi, Greg.

It would be best to take a formal course in programming, but for some more
information about object-oriented programming, please see the following Web
pages:

http://en.wikipedia.org/wiki/Object-oriented_programming

http://searchwinit.techtarget.com/sDefinition/0,,sid1_gci212681,00.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"'69 Camaro" <ForwardZERO_SP AM.To.69Camaro@ Spameater.orgZE RO_SPAMwrote in
message news:OrednaR4Fd ZBgizYnZ2dnUVZ_ rqhnZ2d@adelphi a.com...

Hi, Greg.
>

>Originally, I placed the function in a "FormModule " which I think is a
>class module?

>
Yes.
>

>But as I mentioned, it worked in a Msgbox against 1-Record.

>
Inside a VBA procedure, yes. Within the Jet Expression Service that Jet
SQL uses to interpret the user-defined function, no. You have to consider
the context of where the VBA code is being used. It's like if you
traveled to a foreign country where the native tongue isn't English, but
you expect to be able to communicate only in "your" language. When the
language isn't the same, you need to use an interpreter to communicate.
That's what Jet is doing with the Jet Expression Service. This service
interprets the VBA code and returns a value that Jet can then operate on.
But the Jet Expression Service has some limitations, such as only being
able to interpret public functions, not public subroutines, because
subroutines don't return a value, but functions do.
>

>And, I'm not totally sure I understand what "instantiat e"
>means.

>
It's programmer-speak used in object-oriented programming. It means that
a programmer has used code to define an object which has properties
(things that describe the object, such as size, color, et cetera) and
methods (things the object can do, such as open a file, change a value in
a column, et cetera). The complete code definition of an object is called
a class.
>
When the class is needed to do something in the program (such as
manipulate files on the hard drive or display something on your monitor),
the programmer uses code to place an "instance" of that object in memory,
so that the computer has a physical memory address to refer to in order to
read and write the properties of that object and execute the methods of
that object. This "instance" of the object (and there can be many other
instances running at the same time) is called instantiation of the object
as soon as it's placed into memory. The object "instance" is placed into
memory so that the computer can use the object's code to carry out
whatever the programmer requires the object to do.
>
HTH.
Gunny
>
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
>
>
<ApexData@gmail .comwrote in message
news:1169230318 .066848.124910@ l53g2000cwa.goo glegroups.com.. .

>>

>>Excellent! That's the standard module I was talking about. All the
>>other
>>modules in the database application are class modules, and unless an
>>object
>>of that class is instantiated, Jet can't use anything defined in that
>>class
>>(such as procedures or properties) in the Jet Expression Service.
>>Unfortunately , one cannot instantiate an object within Jet SQL. That
>>can
>>only be done within a VBA procedure, so that's why the public function
>>must
>>be defined in a standard module, where it's "visible" to Jet.

>>
>Originally, I placed the function in a "FormModule " which I think is a
>class module?
>This was when it would not work with SQL. Or, Jet is guess. (Private
>or Public no work)
>But as I mentioned, it worked in a Msgbox against 1-Record.
>>
>When placed in an "Module Object" Public module or as you mentioned
>Standard Module
>it worked. Thankyou for the advice. I need to understand modules
>better. And, I'm not totally sure I understand what "instantiat e"
>means.
>>
>ThanksAgain "Gunny"
>Greg
>>

>
>

[ApexData@gmail.com]

Re: SQL Undefined Function Error

Gunny

>From my perspective, Access offers an incedible environment for

development, but it comes at a cost. The Language and Syntax learning
curve. A little bit of VB,VBA,SQL,Jet, Access can become daunting at
time.

Thankyou for the incedible lesson and information.
You have obviously mastered the melting pot.

ThanksAgain
Greg

['69 Camaro]

Re: SQL Undefined Function Error

You're welcome, Greg. Good luck with it.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


<ApexData@gmail .comwrote in message
news:1169246836 .765185.319760@ s34g2000cwa.goo glegroups.com.. .

Gunny
>
Thankyou for the incedible lesson and information.
You have obviously mastered the melting pot.
>
ThanksAgain
Greg
>