vba - Escaping ' in Access SQL

Question

I'm trying to do a domain lookup in vba with something like this:

DLookup("island", "villages", "village = '" & txtVillage & "'")

This works fine until txtVillage is something like Dillon's Bay, when the apostrophe is taken to be a single quote, and I get a run-time error.

I've written a trivial function that escapes single quotes - it replaces "'" with "''". This seems to be something that comes up fairly often, but I can't find any reference to a built-in function that does the same. Have I missed something?

Answer 1

The "Replace" function should do the trick. Based on your code above:

DLookup("island", "villages", "village = '" & Replace(txtVillage, "'", "''") & "'")