Undefined function 'replace' in expression

[warrio]

Hi there,

I'm trying to get some data from an Access database using Excel.
Once the connection established, I run a query with the Replace function.

The problem, is that I get this error message:
"Undefined function 'replace' in expression".

I think it's a reference that I'm missing, but I don't know which one.

Those I have checked are:
- Visual Basic for Applications
- Microsoft Excel 11.0 Object Library
- Microsoft ActiveX Data Objects 2.8 Library
- OLE Automation
- Microsoft Office 11 Objects Library
- Microsoft Scruotubg Runtime
- Microsoft DAO 3.6 Objects Library

I have NO reference mentioned as Missing!

If you have any suggestion or idea, I'll really appreciate your help.

Thanks in advance.

 

[Matthew Herbert]

Warrio,

Do you have any code to post? It's difficult to decipher "Undefined
function 'replace' in expression" without any related syntax.

Best,

Matthew Herbert

 

[warrio]

Hi Herbert,
Here is the code I use to have the function replace in an sql query:

Sub mySub() ========================================
Dim con as ADODB.Connection, rs AS ADODB.Recordset
Set con = New ADODB.Connection
Con.Provider="Microsoft.Jet.OLEDB.4.0"
Con.Properties("Data Source").Value = "C:\myDb.mdb"
Con.Mode = adModeShareDenyNone
Con.Properties("Jet OLEDBatabase Password") = "pwd"
Con.Open

Set rs = New ADODB.Recordset
rs.Open "SELECT Replace(Table1.Field1, '--','') FROM Table1", Con, 1
End Sub '==========================================

Does it change something to see the code?
And how come that the function Replace works fine in vba (outside the
quotes)?

Thanks in advance.

 

[warrio]

What I understood from an answer given by Michdenis in anther formum is that
not all the functions available in Access are included in the ADO driver.
It's the same for the functions Right, Left,...etc.

Thanks for your help, I'll try to replace the text with a different method.

 

[Matthew Herbert]

Warrio,

Thanks for posting the code. Seeing the code allows others to see exactly
what is going on rather than making an educated guess from your description.
The REPLACE (and RIGHT, LEFT, and other text functions) do exist in VBA and
are very available for use. As you mentioned in your other post, REPLACE
needs to reside outside the string text.

rs.Open "SELECT Replace(Table1.Field1, '--','') FROM Table1", Con, 1

This SQL statement is literally evaluated as SELECT Replace(TAble1.Field1,
'--',")... As I'm sure you are aware, SQL does not have a Replace keyword,
nor do you have a field name entitled "Replace(Table1.Field1...". Seeing as
how you are trying to use the REPLACE function to return the corresponding
field name, you need to concatenate the text accordingly so that the SQL
statement will evaluate correctly (see below). Everything embedded in double
quotes is a literal string.

rs.Open "SELECT " & Replace(Table1.Field1, "'--'", "''") & "FROM Table1",
con, 1

Often times people will create an additional variable, strSQL, to contain
the SQL statement. The variable allows you to ensure the proper statment
text prior to execution. So you could do the following:

Dim strSQL As String
strSQL = "SELECT " & Replace(Table1.Field1, "'--'", "''") & "FROM Table1"
Debug.Print strSQL
rs.Open strSQL, con, 1

Debug.Print will print to the Immediate Window (View | Immediate Window)
where you can view the result of strSQL.

Best,

Matt