Use Single Quote in Access VBA DoCmd Statement

[chalupabatman]

Greetings - I am wanting to use the replace function in access vba to replace all single quotes with null. I have this syntax, but compile error.

DoCmd.RunSQL "update [Vendorlist] 
set [jobdesc] = replace([jobdesc], "'", "")
where [jobdesc] like "*'*";"

[ranman256]

put it in a query.
the query works.

UPDATE Vendorlist SET [jobdesc] = Replace([jobdesc],"'","") WHERE (((tClients.[jobdesc]) Like "*'*"));

[chalupabatman]

put it in a query.
the query works.

UPDATE Vendorlist SET [jobdesc] = Replace([jobdesc],"'","") WHERE (((tClients.[jobdesc]) Like "*'*"));

Requirements need it to be VBA

[ArviLaanemets]

Do you want elliminate all single quotes from field [jobdesc], i.e. replace all single quotes with nothing?

Or do you want to replace all entries in field [jobdesc] equal to "'" with NULL value?

[chalupabatman]

Do you want elliminate all single quotes from field [jobdesc], i.e. replace all single quotes with nothing?

Or do you want to replace all entries in field [jobdesc] equal to "'" with NULL value?

Replace all single quotes from the field [jobdesc] with nothing.

[June7]

Compile error because you are not using concatenation and line continuation. Also, the embedded quote marks are an issue. Don't really need the filter criteria.

If you don't want warning popups, one approach is:

CurrentDb.Execute "update [Vendorlist] set [jobdesc] = replace([jobdesc], Chr(39), '')"

But since Chr() is calling a VBA function:

CurrentDb.Execute "update [Vendorlist] set [jobdesc] = replace([jobdesc], ""'"", '')"

Note use of doubled quote marks to 'escape' the character so the query will recognize as a literal character, not a special character.

Note use of two apostrophes for the embedded empty string, otherwise use four quotes.