Handling ' in variables

[ScottInTexas]

I am reading values from a table and using them to supply values from another table. One such value has the abbreviation Mat'l. When I use this in an SQL I get an error because of the '. For example


Select ChartTitle, ValueField, DataOrder, Barcolor FROM Charts WHERE ChartName='" & ChartType & "'"


In this case ChartType has an ' in the name.

Syntax error (missing operator) in query expression 'ChartName='Raw Mat'l Cost''.


ANy hints or tricks?

[khillabolt]

I use a nifty function to handle this situation:

Function CvrtQteBar(String)
string = Replace(string,"'","''")
CvrtQteBar = Replace(string,"|","' & Chr(124) & '")
end function


So your string would look like:

Select ChartTitle, ValueField, DataOrder, Barcolor FROM Charts WHERE ChartName='" & CvrtQteBar(ChartType) & "'"

Hope this helps...

[glenngv]

or read that sticky thread about single quotes!

http://www.codingforums.com/showthread.php?s=&threadid=9843

[angiras]

why not using directly something like :

SELECT Charts.* FROM Charts WHERE Charts.ChartName IN (SELECT ChartType.ChartName WHERE ChartType.id = 5;);

[whammy]

What glenn said... that sticky post should solve your problems...