Using 'Replace' function in query 2

[fischadler]

Please take a look at the query below:

SELECT CustomerID, LastName, FirstName, Phone1, Phone2, Mobile, Email
From Customers
Where (((Replace([Customers]![Phone1], " ", "")) Like "*5567*")) Or (((Replace([Customers]![Phone2], " ", "")) Like "*5567*")) Or (((Replace([Customers]![Mobile], " ", "")) Like "*5567*")) ORDER BY CustomerID;

Note that this query works fine MS Access. However if I try to get a recordset using:

Set rstCustomers = MyDB.OpenRecordset(strSQL)

Where strSQL is the query above, I get a Visual Basic rubtime error 3085 reading "Undifined function 'Replace' in expression."

Any ideas?

 

[Crowley16]

looks like you're setting your sql string wrong somehow... try useing ' instead of " ...?



Procrastinate Now!

 

[lupins46]

Access version?

 

[fischadler]

Changing the double quotes to single quotes makes no difference (I did try that). I've been using double quotes all the time in VB but never had any problems (altough in ASP I had to use single quotes). As you can see from the error, the DAO database engine has a problem with the "Replace" function which works fine when the query is inserted directly into Access.

My Access version is 2000.

 

[Crowley16]

eh, where exactly do you get the error?

on the line where you set the strSql
or
on the line where you try to open the recordset?

Procrastinate Now!

 

[fischadler]

I am getting the error on the line:

Set rstCustomers = MyDB.OpenRecordset(strSQL)

Somehow the database engine does not seem to work in the same manner as the engine inside MS Access 2000 itself.

 

[Crowley16]

hmm, that is kinda strange...

maybe it's a referencing problem of some sort...

Procrastinate Now!

 

[p27br]

yes, i agree with crowley16

open a VBA module and open tools > references.
See if you have a line containing MISSING:

 

[PHV]

Have you tried to open a saved query instead of the sql string ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fischadler]

PH,
I followed your advice and tried using a saved query but I still got the same Visual Basic error.
Running the query in Access 2000 works fine though.

 

[sxschech]

This function will allow you to use the replace function in a query:

Function replaceqry(FieldName As String, ItemtoReplace As String, ReplaceWith As String)
    'Allow use of Replace Function in queries for those machines that can't
    'use it directly in a query
    '11-Aug-2003
    replaceqry = Replace(FieldName, ItemtoReplace, ReplaceWith)
End Function

change
Where ((([red]Replace[/red]([Customers]![Phone1], " ", ""))

to
Where ((([red]Replaceqry[/red]([Customers]![Phone1], " ", ""))

 

[fischadler]

Where do I enter this function? It has to be run by the database engine, not the Visual Basic application.

 

[lupins46]

'It has to be run by the database engine, not the Visual Basic application'

No possibility of this, I'm afraid. Jet SQL has very few functions and definitely no Replace function.

I don't understand why you don't have a replace function available. If you type Replace into the debug window and press F1 what do you get?
(I assume you are using VB6?)

Pete

 

[fischadler]

I do have the Replace function in Visual Basic and it works fine. However, in this case, the replace function is passed to the database engine as part of a query string. It is the database engine that does not recognize the Replace function, not Visual Basic.

What is driving me nuts is that if I save the string (that is being passed to the DB engine through the 'Set' command) into the clipboard and then paste it inside the SQL window in Access 2000 it works perfectly.

 

[lupins46]

Got it! The replace functions cannot be with in the "" in the stsrql string. The following is an exaggerated split to illustarte the point. I hope i have all the " and ' in the right places.

strsql = SELECT CustomerID, LastName, FirstName, Phone1, Phone2, Mobile, Email From Customers Where ('"
strsql = strsql & Replace([Customers]![Phone1], " ", "")
strsql = strsql & "' Like "*5567*") Or ('"
strsql = strsql & Replace([Customers]![Phone2], " ", "") strsql = strsql & "' Like "*5567*") Or ('"
strsql = strsql & Replace([Customers]![Mobile], " ", "") strsql = strsql & "' Like "*5567*") ORDER BY CustomerID;"

 

[fischadler]

It's not the first time that I use double quote marks inside an strSQL string (I have to when I am using text field values). All I have to do is have 2 double quotes after each other so that Visual Basic is not confused by thinking I'm trying to end the string.
In the example you showed, you are using the Replace function of Visual Basic (which as I said before, works fine). But that is not what I want to do. I want to pass the "Replace" command to the database engine and therefore the word "Replace" has to be inside the strSQL and therefore within the first and last quote mark.

 

[lupins46]

I have already told you that you cannot pass the Replace function.
Jet has no Replace function. The engine you are linking to is the Jet database engine; you are not using 'Access' at all. Access is just a GUI to the Jet engine just as VB can be.

Pete

 

[fischadler]

I don't think MS Access and Visual Basic use the same Jet Database Engine. When I enter the "Replace" command in an SQL string directly in Access it works. Another explanation would be that Access converts the replace command with something understandable by the Jet engine.

Anyway, I am awarding you a star for your efforts - even though there doesn't seem to be a solution.

 

[lupins46]

Access and VB use exactly the same database engine.

Access is not VB is not Delphi is not ...(name your product).

If you wrote the sql statement in an Access VBA module you would have exactly the same issue as you are seeing here.

It's the Access frontend GUI that deals with how it passes the sql string from the Access query builder to JET. The Access query builder does not require functions to be outside the quotes.

There are hunderds of Access GUI features (for example several field properties) that are not implemented in Jet. That's why Access as a product is good for developing database applications and why it's harder work if you are linking to a Jet database from a general purpose programming tool.

I appreciate that MS don't help in clarifying their technology by constantly referring to 'Access' databases.
But the Access product is a tool for building database applications. The native database that it uses is the Jet database but it doesn't have to be. The Access GUI front end can be used to build front-ends to a whole bunch of database technologies in the same way that VB can.

But the Jet engine, which is distributed as part of Access is also distributed as part of VB and you can build Jet-based applications in VB without any reference to Access.

 

[sxschech]

Where do I enter this function? It has to be run by the database engine, not the Visual Basic application.

Not sure if the question on where to place the function still needs to be answered. If so, the function would be saved in a module. Could be a new or existing module.

The replaceqry function was created because we found that for some reason, some of our computers could not run the replace function directly in a query, while they could run it through this function. We have also used this method for other functions that failed to run directly in a query such as instrrev

Function instrrevqry(FieldName As String, ItemtoMatch As String)
    instrrevqry = InStrRev(FieldName, ItemtoMatch)
End Function