SQL Escape Characters for Access

Hi all,

I need to pass the contents of a text box to an Access database, problem is that the textbox could contain any normal character.

What is the escape character so that apostrophies won't cause the end of the string?

Cheers

Dave

For Microsoft databases, you need to convert single apostrophes to double ones ' '

You can use

sql = replace(sql, " ' ", " ' ' ")

I added some spaces for readability

Yes!Doug G'anwser is correct!Plz replace the ' to the ''...

Cheers, but is there an standard rule? Would you always just type the character twice?

The textbox could hold and ASCII character. " ' ", " & ", " % " etc.

Yes,It's SQL statement rule with using "..U can find some knowledge about it in the sql statement guide!...

To escape special characters in LIKE clauses, the general rule is to enclose the character in [], e.g.[*] and [?].

Example:

select * from x where y like "[*]*"

will give you all records from table x where the y field starts with an asterisk (*).

Kind regards,
Jesper Hertel

I guess this must be the necessary escapes for LIKE operator arguments:

[ --> [[]
' --> ''
" --> ""
? --> [?]
* -->[*]
# --> [#]

I guess the ']' character does not need escaping, in contrast to the '[' character.

/Jesper