Access 2000: Undefined function 'replace' in expression.

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?

"Remou" <> schreef in bericht
news:4344ED78-4092-4144-91E3-...
>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

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,Print Date 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" wrote:

>
> "Remou" <> schreef in bericht
> news:4344ED78-4092-4144-91E3-...
> >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" <> schreef in bericht
news:86E2AF38-9F79-4EBB-ABC6-...
> 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,Print Date 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" wrote:
>
>>
>> "Remou" <> schreef in bericht
>> news:4344ED78-4092-4144-91E3-...
>> >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
>>
>>
>>

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,Print Date FROM tmpAnalysis
WHERE

ie a double quote after strSQL =

JimF

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.

"Jim" wrote:

>
> "Remou" <> schreef in bericht
> news:86E2AF38-9F79-4EBB-ABC6-...
> > 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,Print Date 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" wrote:
> >
> >>
> >> "Remou" <> schreef in bericht
> >> news:4344ED78-4092-4144-91E3-...
> >> >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
> >>
> >>
> >>
>
> 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,Print Date FROM tmpAnalysis
> WHERE
>
> ie a double quote after strSQL =
>
> JimF
>
>
>
>
>