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

[emozley]

Hi,

I am building a SQL query on the fly - part of the code is as follows:

AuthorSearch=""
If UBOUND(AuthorArray,1) >= 0 Then AuthorSearch=AuthorSearch & "("
For i=0 To UBOUND(AuthorArray,1)
If ExactMatch=TRUE Then AuthorSearch=AuthorSearch & "' ' " & CHR(038)
AuthorSearch=AuthorSearch & " Replace(Catalogue.Author, ',', ' ') "
If ExactMatch=TRUE Then AuthorSearch=AuthorSearch & CHR(038) & " ' ' "
AuthorSearch=AuthorSearch & "LIKE "
AuthorSearch=AuthorSearch & " '%"
If ExactMatch=TRUE Then AuthorSearch=AuthorSearch & " "
AuthorSearch=AuthorSearch & AuthorArray(i)
If ExactMatch=TRUE Then AuthorSearch=AuthorSearch & " "
AuthorSearch=AuthorSearch & "%' "
If i<UBOUND(AuthorArray,1) Then
AuthorSearch=AuthorSearch & MatchType
End If
Next
If UBOUND(AuthorArray,1) >= 0 Then AuthorSearch=AuthorSearch & ")"

The final SQL query which I display with Response.Write is as follows:

SELECT Catalogue.BookID, Catalogue.Author, Catalogue.Title, Catalogue.Edition, Catalogue.PublicationYear, Catalogue.Publisher, Catalogue.Notes, Catalogue.ClassMarks, Catalogue.Department, Catalogue.ISBN FROM Catalogue WHERE (' ' & Replace(Catalogue.Author, ',', ' ') & ' ' LIKE '% abbey %' )

I execute the code with the following - TBL is my recordset object and DB is my connection object.

set TBL = DB.Execute(strSQL)

If I copy and paste what is displayed with response.write in to my access database as a query it works but when I try to execute the query on the script it comes up with

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

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


Any ideas?

Thanks very much

Ed

 

[onpnt]

Why attempt to use access replace funstions when you can get it done rate there in the code.

I'm the first to say use your database servers functionality for performance and maintaining of your code but in access case I doubt you will gain anything by calling it's functions over vbscript runtime



[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[gmmastros]

Because he is trying to find records.

Suppose he's trying to find books written by Jack London. In the database there could be...

[tt]
Book Author
-------------------- -------------
The Call Of The Wild London, Jack
White Fang Jack London[/tt]

While doing the search, the OP wants to replace commas with nothing so that a Like search on 'London' would return both records.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh yeah... my suggestion to emozley is to stop using the ODBC driver and use OLEDB instead. Take a look here:

http://www.connectionstrings.com/?carrier=access

Modify your connection string to match one of the OLEDB connection strings. You may be pleasantly surprised.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[emozley]

gmmastros - hi it looks like even with an OLEDB connection string I am still unable to use the replace function.

 

[onpnt]

Thanks George. I just didn't see that at first sense it looked like the OP was searching for one word (first or last name)

That wouldn't make sense to do either though so I'm just off

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[DotNetGnat]

yes i had the same problem few years back with using the replace() function on the ASP page...I later had to change my query to a stored procedure(SQl Server) or a stored aquery( MS Access) and then it worked fine...

let me know if you need any further information...

-DNG

 

[emozley]

DNG - could you give me some more info on setting up an aquery in access?

Also someone has helped me with a regular expression that works in Access but is it possible to call this from an ASP page?

Thanks very much

Ed

 

[DotNetGnat]

Access stored query is similar to SQL Server stored procedure...

Let say if you have to do a query which looks like something on the ASP page...

sql = "Select EmpId, EmpName, EmHiredate from Employee WHERE replace(EmpName, ',','') = 'tom' "

then using stored query in Access you do the following...

Step 1: Create a new query(lets says [red]findemployee[/red]) in your Access database something like this

PARAMETERS empname text(255);
Select EmpId, EmpName, EmHiredate from Employee WHERE replace(EmpName, ',','') = [empname]

Step 2: then on the ASP page, you simply have the following

sql = "execute findemployee 'tom' "

OR if you have a form variable to pass then you do...

sql = "execute findemployee '" & request.form("searchemployee") & "' "

-DNG