Menu
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?
390
Escape sequences are used within an SQL statement to tell the driver that the escaped part of the SQL string should be handled differently. When the JDBC driver processes the escaped part of an SQL string, it translates that part of the string into SQL code that SQL Server understands.
The simplest method to escape single quotes in SQL is to use two single quotes. For example, if you wanted to show the value O'Reilly, you would use two quotes in the middle instead of one. The single quote is the escape character in Oracle, SQL Server, MySQL, and PostgreSQL.
If you must use special characters in query expressions, enclose the special characters in brackets ([]). For example, if you want to use the greater than sign (>), use [>].
#Error. The #Error error value means that Access cannot evaluate an expression. For example, you may have supplied incorrect or too few arguments for an aggregate (totals) function, you may have used a parameter query as the domain for an aggregate function, or you may have made a circular reference in the expression.
The "Replace" function should do the trick. Based on your code above:
DLookup("island", "villages", "village = '" & Replace(txtVillage, "'", "''") & "'") If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
