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?

 

[Sektor]

strsql = "Select material from CAS_number where material =2,2'',2""-Trihydroxytriethylamine"

 

[pcc]

That statement would fail as there is no " after the = sign...

 

[Ruddles]

[FONT=Courier New]strsql = [COLOR=red][B]"[/B][/COLOR]Select material from CAS_number where [/FONT][FONT=Courier New]material = [COLOR=blue][B]'[/B][/COLOR]2,2[COLOR=blue][B]''[/B][/COLOR],2[B][COLOR=red]""[/COLOR][/B]-Trihydroxytriethylamine[COLOR=blue][B]'[/B][/COLOR][COLOR=black];[/COLOR][COLOR=red][B]"[/B][/COLOR][/FONT]

Double quotes are in red, single quotes are in blue.

Any good?

 

[pcc]

Thanks for reply but the problem is not with the sql statement itself, it's how to generate it on-the-fly using VBA. I've decided for now to cheat a bit and simply replace " with $ in the substance name so that
2,2′,2″-Trihydroxytriethylamine

becomes

2,2′,2$-Trihydroxytriethylamine

I can then convert back during reporting to yield the original name. It's not ideal but I can't spend any more time worrying about it. Thanks for inputs anyhow....

 

[Ruddles]

Yes, I'm aware the problem is with the VBA rather than the SQL - that's why I'm trying to correct the VBA.

If you place a breakpoint immediately after the strsql= statement and examine the contents of strsql in the Immediate window (Ctrl-G), it should be obvious where the problem lies. To do this, type ?strsql and hit Enter.

Did you try my code correction?

 

[RoryA]

strsql = "Select material from CAS_number where material ='" & replace(substance, "'", "''") & ";"

or you can probably also use (assuming the data doesn't have hashes in):

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

 

[pcc]

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. this will be interpreted as
Select material from CAS_number where material = '2,2'
then there will be
',2""-Trihydroxytriethylamine'; after it, and this will cause an error

I appreciate your help but as I said I can't spend any more time on it as there are more pressing problems to resolve. I have parked it for now, substituting " with $ and this will do.

Thanks

 

[pcc]

Rory can't get that to work either I'm afraid.
Please don't worry about it any more I don't want to waste any more time. I thought there might be a quick fix but it's not important enough to pursue any more.
Cheers

 

[RoryA]

May I politely suggest that you do actually try it.

Edit: I see you did. It (the first one)works for me using your sample.