Access 2000: Undefined function 'replace' in expression.

[Remou]

I am using Access 2000 sp1 with Sp2 and Sp3 installed and the Vista version
of Jet on a Vista laptop. The above error is returned when using the replace
function in a query, although it is available in VBA. Does anyone know if
there is a fix for this?

 

[Jim]

I am using Access 2000 sp1 with Sp2 and Sp3 installed and the Vista version
of Jet on a Vista laptop. The above error is returned when using the
replace
function in a query, although it is available in VBA. Does anyone know if
there is a fix for this?

If you also posted the query, it might help.

JimF

 

[Remou]

Thank you for your reply.

I was referring to any query, rather than a specific query, and I was hoping
for a hotfix, rather than a code fix. I am using a UDF at the moment to deal
with the problem, but it is tedious, because the computer on which the
database is to be used allows Replace in a query.

Here is an example of using Replace, and it does not work:

'========================================
strSQL = "SELECT Format(Nz(Member,False),'Yes/No') As
Mem,Title,Forename,Surname,InvoiceNumber,InvoiceTo," _
& "Replace(InvoiceToAddress,(Chr(13)+Chr(10)),'<BR>') As
Address,Amount,InvoiceDate,PaidDate,AuthDate,PrintDate FROM tmpAnalysis WHERE
" _
& Me.Filter
'========================================

However, if I change Replace to refer to this UDF all is well:

'========================================
Function FindAndReplace(ByVal strInString As String, _
strFindString As String, _
strReplaceString As String) As String


FindAndReplace = Trim(Replace(strInString & " ", strFindString, _
strReplaceString))

End Function
'========================================

As you can appreciate, Replace is a very useful function in queries, and I
would prefer not to have to add a UDF to every new DB.

 

[Jim]

Thank you for your reply.

I was referring to any query, rather than a specific query, and I was
hoping
for a hotfix, rather than a code fix. I am using a UDF at the moment to
deal
with the problem, but it is tedious, because the computer on which the
database is to be used allows Replace in a query.

Here is an example of using Replace, and it does not work:

'========================================
strSQL = "SELECT Format(Nz(Member,False),'Yes/No') As
Mem,Title,Forename,Surname,InvoiceNumber,InvoiceTo," _
& "Replace(InvoiceToAddress,(Chr(13)+Chr(10)),'<BR>') As
Address,Amount,InvoiceDate,PaidDate,AuthDate,PrintDate FROM tmpAnalysis
WHERE
" _
& Me.Filter
'========================================

However, if I change Replace to refer to this UDF all is well:

'========================================
Function FindAndReplace(ByVal strInString As String, _
strFindString As String, _
strReplaceString As String) As String


FindAndReplace = Trim(Replace(strInString & " ", strFindString, _
strReplaceString))

End Function
'========================================

As you can appreciate, Replace is a very useful function in queries, and I
would prefer not to have to add a UDF to every new DB.

It is a long time since I did any programming in Access and maybe I am just
being stupid but perhaps the following might help:

strSQL = ""SELECT Format(Nz(Member,False),'Yes/No') As
Mem,Title,Forename,Surname,InvoiceNumber,InvoiceTo," _
& "Replace(InvoiceToAddress,(Chr(13)+Chr(10)),'<BR>') As
Address,Amount,InvoiceDate,PaidDate,AuthDate,PrintDate FROM tmpAnalysis
WHERE

ie a double quote after strSQL =

JimF

 

[Remou]

Replace does not work regardless of the query with which it is used. Replace
does not work when used in the Query design screen. Sadly, replace does not
even work with VBScript and ADO. The same error is returned in each case,
that is, Undefined Function.

The SQL of the query is proving to be a red herring, I fear.