Escaping ' in Access SQL

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

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

It's worse than you think. Think about what would happen if someone entered a value like this, and you haven't escaped anything:

'); DROP TABLE [YourTable]

Not pretty.

The reason there's no built in function to simply escape an apostrophe is because the correct way to handle this is to use query parameters. For an Ole/Access style query you'd set this as your query string:

DLookup("island", "village", "village = ? ")

And then set the parameter separately. I don't know how you go about setting the parameter value from vba, though.

Though the shorthand domain functions such as DLookup are tempting, they have their disadvantages. The equivalent Jet SQL is something like

SELECT FIRST(island)FROM villagesWHERE village = ?;

If you have more than one matching candidate it will pick the 'first' one, the definition of 'first' is implementation (SQL engine) dependent and undefined for the Jet/ACE engine IIRC. Do you know which one would be first? If you don’t then steer clear of DLookup :)

[For interest, the answer for Jet/ACE will either be the minimum value based on the clusterd index at the time the database file was last compacted or the first (valid time) inserted value if the database has never been compacted. Clustered index is in turn determined by the PRIAMRY KEY if persent otherwise a UNIQUE constraint or index defined on NOT NULL columns, otherwise the first (valid time) inserted row. What if there is more than one UNIQUE constraint or index defined on NOT NULL columns, which one would be used for clustering? I've no idea! I trust you get the idea that 'first' is not easy to determine, even when you know how!]

I've also seen advice from Microsoft to avoid using domain aggregate functions from an optimization point of view:

Information about query performance in an Access databasehttp://support.microsoft.com/kb/209126

"Avoid using domain aggregate functions, such as the DLookup function... the Jet database engine cannot optimize queries that use domain aggregate functions"

If you choose to re-write using a query you can then take advantage of the PARAMETERS syntax, or you may prefer the Jet 4.0/ACE PROCEDURE syntax e.g. something like

CREATE PROCEDURE GetUniqueIslandName(   :village_name VARCHAR(60))AS SELECT V1.island_name  FROM Villages AS V1 WHERE V1.village_name = :village_name       AND EXISTS        (        SELECT V2.village_name          FROM Villages AS V2         WHERE V2.village_name = V1.village_name         GROUP             BY V2.village_name        HAVING COUNT(*) = 1       );

This way you can use the engine's own functionality -- or at least that of its data providers -- to escape all characters (not merely double- and single quotes) as necessary.