VBA problem with String concatenation

Question

I want to create a query like this :

SQL = SQL + "Libelle = \"" + Me.Libelle + "\" AND "

because if I have a problem with my data if I write like this :

SQL = SQL + "Libelle = '" + Me.Libelle + "' AND "

but there is a problem with the \" I'm used to use it in Java (I'm not a VBA developper :s) How to resolve it ? thnx

Answer 1

The solution is a concatenation with the Chr(34) :)

SQL = SQL + "Libelle = " + Chr(34) + Me.Libelle + Chr(34) + " AND "

Answer 2

In VBA use & to concatenate strings, + will lead to unexpected results. Jet SQL uses two single quotes to escape a single quote. It is normal to use Replace to get this.

 SQL = SQL & "Libelle = '" & Replace(Me.Libelle,"'","''") & "' AND "

Answer 3

In VB you get a literal quote with two quotes. "abc""def" yields abc"def.

What you want is:

SQL = SQL & "Libelle = """ & Me.Libelle & """ AND "