• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use sql command Where in Command to include a text that contains an apostrophe

A

Abdulassees

New Member
Hi Team,

I am looking for a particular issue.

I am using below sql code for pulling the data. All looks good however the field name which has single apostrophe is making problem. Please find the code below. Please help me.

strSQL = "Select [Firm],[ID],[Name],[Last Approval Date] FROM [" & strOpps & "$]" & _
"WHERE [Firm] IN ('INDIA','PAKISTAN','CAMEROON','CONGO','COTE D' IVORY','ZIMBABWE')"

I have a problem with Cote D' Ivory due to the apostrophe. May I know any solution for the same.

Regards,
Abdul
 
Hi Chihiro,

Thank you for the reply.
The code is not throwing any error message, however the records for Cote D'Ivory is not picking in the code. Please help.

Regards,
Abdul
 
Then try multiple Or with LIKE instead of using WHERE IN (since you can use LIKE with IN).
Something like...
Code: 
WHERE
[Firm] = 'INDIA' OR
[Firm] = 'PAKISTAN' OR
...
[Firm] Like 'COTE D%IVORY' OR
[Firm] = 'ZIMBABWE'

Alternately test just for literal string for COTE D'IVORY and see if you get any data.
 
Is your column set to varchar with specific length? Sometimes you need to add "%" wildcard at end of the string. Otherwise, you'd need to query the data without the criteria and look at the data and test.

Try various query string for Cote D'Ivory.
'COTE%', '%IVORY', 'COTE%IVORY%' etc, etc.
 
Thanks a lot Chihiro,

The below code worked perfectly which you provided earlier.
It was my bad that I used spell mistake. Thank you again for your great support. :)

WHERE
[Firm] = 'INDIA' OR[Firm] = 'PAKISTAN' OR...
[Firm] Like 'COTE D%IVORY' OR[Firm] = 'ZIMBABWE'

Regards,
Abdul
 
You must log in or register to reply here.
Back
Top