Replace Function

[Mark Cooney]

Hi all,

I had a db on SQL 2000 I was using Replace(email,'@','') and it was fine
however I have brought it down to an Access db and the following error
occurs on an asp page.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in
expression

How can I make the Replace function work

Thanks

 

[Dirk Goldgar]

Hi all,

I had a db on SQL 2000 I was using Replace(email,'@','') and it was
fine however I have brought it down to an Access db and the following
error occurs on an asp page.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function
'Replace' in expression

How can I make the Replace function work

I'm not familiar with the workings of ASP pages, but I believe the
problem is that, while the REPLACE() function is built into T-SQL, it
isn't part of Jet SQL. Access 2000 and after does have a VBA Replace()
function, but only queries actually executed from an Access application
will be able to use this function in SQL. That's because Access makes
an expression service available that is not a normal part of Jet.

I could be wrong, but I don't think your ASP page is gong to be able to
get the Replace function to be recognized and evaluated by the database
engine. How about sending a SQL statement that doesn't use the
Replace() function, but using the VBScript Replace() function on the
data that is received back from the query?