Variable contains ' and " - help!!

[pcc]

I have an Access database that contains names of chemical compounds, and am using SQL (generated via VB in Excel) to add records to/extract records from the database.

Some compound contain single and double quotes on their names eg
2,2′,2″-Trihydroxytriethylamine

This is causing a problem in parsing the SQL since, if I use a single ' as a text delimiter, it clashes wiith the ' in the substance name, and I get an error, but if is use " as a delimiter I get the same problem:

strsql = "Select material from CAS_number where material =""" & substance & """;"

gives a problem because this resolves to

Select material from CAS_number where material ="2,2′,2″-Trihydroxytriethylamine"

strsql = "Select material from CAS_number where material ='" & substance & "';"

gives a problem because this resolves to

Select material from CAS_number where material ='2,2′,2″-Trihydroxytriethylamine'

Can anyone advise a way out of this conundrum?

 

[Ruddles]

Didn't try code as it will fail because when
Select material from CAS_number where material = '2,2'',2""-Trihydroxytriethylamine';
is executed, there will be an imbalance in the quotes.

I don't think there is because, in the same way as you embed a double quote in a VBA string by doubling it up ("This "" is a double quote"), you embed a single quote in a SQL string in the same way ('This '' is a single quote').

All the quotes in my suggested method do appear to me to be matched and correct. I've tested it here and it works. I'm disappointed that you dismissed it without trying it on the grounds that "it will fail because... there will be an imbalance in the quotes" when you admit that you don't actually know how to handle embedded quotes.

I would urge you to resolve this when you do get a spare few moments, if only to satisfy yourself that if you need to do something similar in the future, you'll be able to cope with it and not look for a workaround. It's not always possible to choose what characters your database contains!

 

[pcc]

Yes, apologies.
I was a bit pressed for time yestersday but have found time to try it out now, and it works fine. Thanks for your perseverance ruddles and rorya, sorry for being a bit terse yesterday.
Problem now solved.
Thanks

 

[Ruddles]

S'okay, I'm glad you sorted the problem. Embedded quotes - you either understand them sort of instinctively or they have you !



The feedback's appreciated. I hope I didn't sound too grumpy!