undefined function replace in expression

[NiteFly]

Hello
i've read all the threads dealing with the subject.
I've written my wrapper to the native replace function.it works fine
inside access.
i've changed sandbox value to 0.
but the problem still persists.
in addiction now there is no REPLACE function, but MyReplace.
this is the access code
Option Compare Database

Public Function MyReplace(StringToSearchIn As String, _
StringToFind As String, _
ReplaceString As String, _
Optional StartHere As Integer = 1, _
Optional HowMany As Integer = -1, _
Optional CompareMode As VbCompareMethod = vbTextCompare) As String

MyReplace = Replace(StringToSearchIn, StringToFind, ReplaceString,
StartHere, HowMany, CompareMode)

End Function

Public Function DoNot() As Integer
DoNot = 1
End Function



this is my vb6 code

Dim aCnn As New ADODB.Connection
Dim aRst As New ADODB.Recordset
Dim SQL As String
Dim dq As String
Dim sa As String
Dim Filtro As String
Dim RepFiltro As String

dq = Chr(34)
sa = Chr(39)
Filtro = dq & sa & dq
RepFiltro = dq & dq

On Error GoTo 0
aCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Sorgenti
\_Mei\Eco.mdb;Persist Security Info=False"
SQL = "Select * from Tab_Pazienti where MyReplace(Nome, " & Filtro
& "," & RepFiltro & ")=" & dq & "Massimo" & dq
aRst.Open SQL, aCnn, adOpenKeyset, adLockOptimistic, adCmdText


last statement raises error "undefined function MyReplace in
expression"

if i change last one with ' SQL = "Select donot()" ' i get
"undefined function donot in expression".
this is driving me crazy.

If i execute the SAME queries inside ACCESS all work fine.

where is my mistake ?
Please help me.

 

[Douglas J. Steele]

You cannot use user-defined functions in queries unless you run them from
inside of Access. This is because when you're running queries from outside
Access, you're only going through the Jet Engine, and it doesn't know
anything about user-defined functions.

 

[NiteFly]

On 15 Mar, 05:12, "Douglas J. Steele"

This is because when you're running queries from outside
Access, you're only going through the Jet Engine, and it doesn't know
anything about user-defined functions.

Thanks 4 your kind reply.
OK, if there is no other way, i'll change db.
Thanks again

 

[Jamie Collins]

I've written my wrapper to the native replace function.it works fine
inside access.
i've changed sandbox value to 0.
but the problem still persists.
in addiction now there is no REPLACE function, but MyReplace.
this is the access code

SQL = "Select * from Tab_Pazienti where MyReplace(Nome, " & Filtro
& "," & RepFiltro & ")=" & dq & "Massimo" & dq

Jet does not have a REPLACE() function. In VBA, Replace() is part of
VBA6 and because Jet development stopped short it only has the VBA5
functions (yes, it really is that old). The absence of Replace is more
of a pain in SQL statements INSERT and UPDATE than in queries
(SELECT), based on the idea that if you are having to 'replace'
something every time it comes out of the database then it probably
shouldn't be in there at all.

I have some code which uses a Sequence table of integers to parse text
data (http://groups.google.com/group/microsoft.public.access/msg/
ec314002d0c63764) but as your need is in the WHERE clause I suspect
you could use nested INSTR()'s.

Your design (SQL DDL) should make your queries (SQL DML) easy to
write. If you find you must use 'replace' to perform a simple query
then you should take a serious review of your design, rather than
switching to another db in the first instance. FWIW my Italian is non-
existent but replacing a filter in a patient's name and returning the
literal string "Maximum" could also indicate a design problem.

Jamie.

--