I have an access 2000 database that I am connecting to using the following connection string:
rs.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=P:\EDD\VVL\ValidValues.mdb ;Jet OLEDB:Database"
No problem accessing it until I tried to use the REPLACE() function in a SQL statement.
SELECT replace(casvv,chr(39),chr(33)) FROM tblCASSTD;
The REPLACE() works as expected when open the table in MSACCESS but does not work when I try to open the DB in VB6.0 code. Gives the follow error: undefined function 'replace' in expression. Can I use the REPLACE() function in SQL when using ADODB recordsets? If not, is there an alternative function?
The reason I am using the REPLACE in the first place is because I have single quotes in my search field and it returns an error when I use try to use the rs.filter that has a single quote. So, I replace the single quotes with a different character and the filter works fine. I tried using two single quotes together, but that does not seem to work in the filter either. Thanks in advance.