SQL ESCAPE 1

[jordanking]

Hello,

Does Access 2003 ADO sql support the "ESCAPE" word

ie

SELECT tblInstalation.idsInstID, tblInstalation.txtAddress
FROM tblInstalation
WHERE (((tblInstalation.txtAddress) Like '%o\'c%') ESCAPE '\';

This is not working for me and I am just wondering if the word escape is even supported or if I am having syntax issues.

The reason is, i am quering a txt field, changing that field and then executing an update query with the new value into the original table. As a result i have to create the update query sql dynamically. However, sometimes the new text field contains an apostrophe and it breaks the syntax by adding an unmatched single quote.

Thanks in advance

JK

 

[PHV]

You have syntax issue: the ESCAPE chararacter could be followed by only % or _ or the ESCAPE char itself.

what about this ?

SELECT idsInstID, txtAddress
FROM tblInstalation
WHERE txtAddress) Like '%o''c%'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jordanking]

sorry, I should have been more clear,

I need to be able to have a single quote in te middle of the text field in an update query.

here is an example of what failed:

UPDATE tblInstalation SET tblInstalation.txtAddress = '152 O'Connor Ave' WHERE (((tblInstalation.idsInstID)=74436));

This is pulled from a loop that goes through 50,000 address records. Only 10-20 have an apostrophy in the actually address like above. It causes the syntax to break.

So I am wondering if there is a way to keep the apostrophy as is by escaping it, or do i have to remove it?

Thanks PHV

 

[PHV]

I already gave you the solution in my previous post ...
UPDATE tblInstalation SET txtAddress = '152 O'[!]'[/!]Connor Ave' WHERE idsInstID=74436

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jordanking]

Thanks,

I was not reading with enough attention, it works

JK