I recently encountered a problem that I’ve actually encountered millions of times before – trying to run an SQL string from VBA code which makes use of double apostrophe’s (“”). Naturally, VBA uses these to break from a string literal, but pure Jet-SQL uses them for functions like Format() and Nz().
So if you do something like this:
strSqlCode = “Select format([field1], “Currency”) from table1″
You will get a nice friendly Access message indicating that your SQL is faulty. Surely that ain’t the case!
There are several different solutions and here is one I find the most handy – using chr$(34)!
Reformat your SQL like this:
strSqlCode = “Select format([field1], ” & Chr$(34) & “Currency” & Chr$(34) & “) from table1”