Using the REPLACE( str, str, str) command in a SQL statement for a adodb.recordset

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.
 
jyamazawaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
Interesting.

Replace wasn't supported in Access SQL when 2000 first came out (but then many things in it were well dodgy to begin with).
But it got a lot better.  (And Jet is doing the opening anyway - and as long as it can read it... it will do so from any source)

Opening the recordset from VB6 shouldn't make a big difference (I wouldn't have thought so anyway).
Are you using *exactly* the same code from there?
GRayLCommented:
If you have a string:

var="This is 'my' string"
and you replace the single quotes with a double quote you will wind up with:

var="This is "my" string" - which will not compute!!

You must replace a single quote with double double-quotes

In the immediate pane type:

var="This is ""my"" string"
? var
This is "my" string

Could this be the problem?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jyamazawaAuthor Commented:
Here is the actual connection code where I get the error:

    Set cVVL = New ADOdb.Connection
    Set rVVL = New ADOdb.Recordset
       
    cVVL.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=P:\EDD\VVL\ValidValues.mdb;"
    cVVL.Open
    'MAINTAIN RECORDSET CONNECTION
    rVVL.Source = "SELECT replace(VV, chr(39), chr(33)) as CASVV,CLIENTVV,[CAS_NO],[ALTVV],[ABBR],[CASASID],[VVLTYPE],[VVLLIST]from joinAll WHERE [VVLTYPE] = "CLIENT_DIOXIN" AND ucase(VVLLIST) = "CASE_COMPONENTS" ORDER BY [VV];"
    rVVL.CursorType = adOpenStatic
    rVVL.LockType = adLockOptimistic
    rVVL.ActiveConnection = cVVL
    rVVL.Open Options:=adCmdText

I do have to add to this that I often use two machines at work, one with MSAccess 2000 and the other with 2003.  The database was created with 2000, but I've been doing most of my development work on the machine with 2003.  After the first comment, I checked and went to the 2000 machine and yes, replace does not work on that machine when I manually run a query.  Sorry for the confusion since I checked my code using 2003 on a 2000 database.  So, does MSACCESS 2003 have a different provider that I can use which allows me to access the extended SQL statements.

I tried using a double-quote " instead of two '' single-quotes and it still did not return any of the data from my recordset with single-quotes in the field.

An alternative I can use an oracle table with the same data in it, single-quotes and all, and connect using an ODBC connection.  Would doing that allow me to use the replace() function?
GRayLCommented:
Could you show us where you set the value of VV?
Leigh PurvisDatabase DeveloperCommented:
Does that compile?
With the double quotes in your string?
DId you try Ray's doubling up - but for the criteria

rVVL.Source = "SELECT replace(VV, chr(39), chr(33)) as CASVV,CLIENTVV,[CAS_NO],[ALTVV],[ABBR],[CASASID],[VVLTYPE],[VVLLIST] from joinAll WHERE [VVLTYPE] = ""CLIENT_DIOXIN"" AND ucase(VVLLIST) = ""CASE_COMPONENTS"" ORDER BY [VV];"

You shouldn't need the UCase though to match case.
So what about

rVVL.Source = "SELECT replace(VV, ""'"", ""!"") as CASVV,CLIENTVV,[CAS_NO],[ALTVV],[ABBR],[CASASID],[VVLTYPE],[VVLLIST] from joinAll WHERE [VVLTYPE] = ""CLIENT_DIOXIN"" AND VVLLIST = ""CASE_COMPONENTS"" ORDER BY [VV];"

JoinAll is some sort of table/query?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.