1

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

Improve this question

3 Answers 3

Reset to default
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 "
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In VBA and Jet/ACE SQL the & concatenation operator ignores Null (Null & "" = "") while the + concatenation operator propagates Nulls (Null + "" = Null). Null concatenation is quite useful in expressions like Mid(("12 + LastName) & (", " + FirstName), 3), but you have to be careful not to try to use it with numeric fields (or strings that can be implicitly coerced to numeric values), since while "12" + Null will propagate the Null, "12" + "8" MAY add the numeric values of the two strings (it depends on the context).
0

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

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

2 Comments

You should not use + to concatenate strings.
You should use the + concatenation operator if you want to propagate Nulls and the & if you don't.
0

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 "
Improve this answer

4 Comments

Do not use plus (+) to concatenate strings in VBA unless you understand what happens if one of the strings is null.
The ANSI standard for SQL specifies single quotes (').
Well, use the + concatenation operator if you want to propagate Nulls. I use it frequently, but you have to be careful (as my extensive comment above explains).
Why should I as an Access developer care particularly if the ANSI standard for SQL uses single quotes? Access doesn't, so isn't that more relevant than an outside standard that is not implemented in Access?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.