Excel 2010 ADO SQL syntax error on name with apostrohpe O'Mally etc 1

[DougP]

Error # -2147217900 syntax error
this is my code orginal code which is creating the "Where" part.
Is there another way to get around apostrohpe in a SQL string?

            SQLStringLaborUpdateWhere = " Where [Resource name] = [highlight #FCE94F]'" & ResourceName & "'[/highlight] And " & _
                                    "[Cost Tracker] = '" & CostTracker & "';"
which makes this complete SQL string.
Update [Time$] Set  [January Labor] = 452.97, [February Labor] = 452.97, [March Labor] = 452.97, [YTD Labor] = 1358.91 Where [Resource name] = [highlight #FCE94F]'O'Mally'[/highlight] And [Cost Tracker] = 'COPD0161';

I changed it to this which works in a Select in Powerpoint but in Excel in an Update it does not work.

            SQLStringLaborUpdateWhere = " Where [Resource name] = [highlight #FCE94F][/highlight]" & chr(34) & ResourceName & chr(34) & "[/highlight]"  And " & _
                                    "[Cost Tracker] = '" & CostTracker & "';"

Update [Time$] Set  [January Labor] = 452.97, [February Labor] = 452.97, [March Labor] = 452.97, [YTD Labor] = 1358.91 Where [Resource name] = "O'Mally" And [Cost Tracker] = 'COPD0161';

DougP

 

[Andrzejek]

Try:

SQLStringLaborUpdateWhere = _
" Where [Resource name] = '" & [blue]Replace([/blue]ResourceName[blue], "'", "''")[/blue] & "' And " & _
"[Cost Tracker] = '" & CostTracker & "';"

Have fun.

---- Andy

 

[DougP]

DougP

 

[Andrzejek]

I use the Replace trick anywhere where there is a possibility of an apostrophe: names, notes, comments, etc. In Selects, Inserts, Updates, Deletes, etc.

Unless you want to go with parameterized queries…


Have fun.

---- Andy