apostrophe trouble

[Sveno]

Hi,

I am attempting to check first name and surname details from an Access table (from a recordset) against names held in an ORACLE database and am having trouble when the surname contains an apostrophe.

The code I am using is:
SELECT * FROM CLIENT (ODBC table) WHERE FIRST_NAME =
'" & RS!FIRST_NAME & "' AND SURNAME = '" & RS!SURNAME & "'"

I have tried:
AND SURNAME = '" & REPLACE(RS!SURNAME,"'","''") & "'

and also:

AND SURNAME = " & chr$(34) & RS!SURNAME & chr$(34) & "

to no avail. Any suggestions would be greatly appreciated.

Cheers

Mark

 

[lupins46]

try...

AND SURNAME = """ & RS!SURNAME &

 

[Sveno]

Thank you for replying, I am still getting the error though. I use the replace statement in SQL server and it works , so this may be something to do with ORACLE.

I am going to ask for permission to create temporary table directly on the ORACLE database, so this will eventually solve the problem.

Cheers

Mark

 

[TomCologne]

ORACLE uses single quotes/apostrophes for strings.

TomCologne