How to compare text value that has special character using SQL in VBA

[kailashxl]

I am using ADODB in Excel and writing SQL query to compare names form one source excel file with that of other excel file. However therein I got stuck as couple of name in the source file having special character like Mike'O Brian. it's throwing error "Syntax Error (missing operator) in query expression. Any help around this would be highly appreciated.

 

[Chihiro]

Are you using the name in the query?

If so, you need to escape single quote by using 2 single quotes.

Ex: '' instead of '

 

[kailashxl]

I am currently working on the data where we have the hierarchy of members which might go up to any level (sample data and required output is shown below. The sample data is having limited rows but the actual data will have rows running in thousands and have to showcase the result upto say 5, 6 or may be more levels.

Any help on adapting the most optimized approach for getting the desired result using Excel VBA (I am trying here to use ADODB SQL). Please suggest/share your thoughts on the approach plan.
Thanks in advance for your help.

member ID	Member Name	Lead_ID	City
Lead1	Leader		City 1
Member1	Member One	Lead1	City 2
Member2	Member Two	Lead1	City 22
Member3	Member Three	Lead1	City 2
Member11	Member Eleven	Member1	City 3
Member12	Member Twelve	Member2	City 4
Member13	member Thirteen	Member1	City 3
Member17	Member Seventeen	Member1	City 7
Member 9	Member Nine	Member13	City 9
Required Outcome:
Top Level	Level1	Level2	Level3	Level4	City
Leader					City 1
Leader	Member One				City 2
Leader	Member One	Member Eleven		City 3
Leader	Member One	Member Thirteen		City 3
Leader	Member One	Member Thirteen	Member Nine	City 9
Leader	Member One	Member Seventeen		City 7
Leader	Member Two				City 22
Leader	Member Two	Member Twelve		City 4
Leader	Member Three				City 2

 

[kailashxl]

Are you using the name in the query?

If so, you need to escape single quote by using 2 single quotes.

Ex: '' instead of '

Thanks Chihiro for your revert- Tried that but not working for me. If your could explain this with example. Thx

 

[Chihiro]

Without sample hard to help. Escaping single quote in SQL statement is standard practice.
See link.

How to Escape Single Quotes in SQL

Do you need to escape special characters or escape a single quote in SQL? Learn how to do that in this article.

www.databasestar.com