422,530 Members | 1,031 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Quotes (') and Double-Quotes (") - Where and When to use them

NeoPa
Expert Mod 15k+
P: 30,680
Background
Whenever code is used there must be a way to differentiate the actual code (which should be interpreted directly) with literal strings which should be interpreted as data. Numbers don't usually have this problem but Dates can too.
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Me.ControlName
  2. refers to a control on a form.  Whereas,
  3. Debug.Print "Me.ControlName"
  4. simply prints the literal text
  5. Me.ControlName
Where to Use Each Quote Type (' or ")
In some places (details to follow) either can work. I'm hoping to convince you that using the appropriate type for where you use it will be in your best interest - for clarity and consistency. Clarity, because reading code where strings of both types are required can be very confusing (Reference so many posts that have this as their basis) without this extra visual clue.
A string in VBA requires the Double-Quote (") to delimit it. Single-Quotes (') won't work.
A string in SQL can use either in most circumstances. The ANSI standard for SQL specifies that it should be a Single-Quotes ('), but MS Access, in its wisdom, decided to be more flexible and support both quote types. This allows less experienced users to get in and use it more easily. It also means they can't get very far before they get confused :(.
I strongly recommend to stay with the standard here as it will make those confusing string manipulations for SQL SO much easier to get to grips with.

Embedded Quotes (General)
From time to time, you will come across the requirement to specify a string which contains the character which you are using to delimit your string (' or ").
The recommended way to handle this is to double up on the quote used.
Assuming you want to assign the text in brackets to the strDisp string - [Please select "Bob" from your list.], you would say :
Expand|Select|Wrap|Line Numbers
  1. strDisp = "Please select ""Bob"" from your list."
It is also possible, in some circumstances, to use the 'other' quote character instead :
Expand|Select|Wrap|Line Numbers
  1. strDisp = "Please select 'Bob' from your list."
This is easier, but I would recommend to steer clear of this method when SQL or other interpreting engines are involved.

Building Strings for SQL
In MS Access though, we come across the situation where we need to use strings that include embedded strings. Particularly with SQL code.
The requirement is to build a string in VBA (SQL instructions) which may then contain string literals to be interpreted by SQL. The SQL should end up something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [TableName] WHERE ([AccountName]='Hieronymous')
This is straightforward enough with the correct quotes used :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM [TableName] WHERE ([AccountName]='Hieronymous')"
People start to get confused when building up a string. Typical requirements for this are line continuation (SQL strings can be very long and messy) and for populating the WHERE clause with string or date items from a form.
Assuming we are creating this string from within the form's module and we have a ComboBox (cboAccount) with the names of the account, then our code would look something like this (_ at the end of a line means to treat the next line as a continuation of the current one.) :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT *" & VbCrLf & _
  2.          "FROM [TableName]" & VbCrLf & _
  3.          "WHERE ([AccountName]='" & Me.cboAccount & "')"
Assuming that Hieronymous has been selected from the list, this would result in exactly the same string being assigned to strSQL.
This string can then be passed to the SQL interpreter
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL strSQL
and the SQL interpreter then handles the embedded literal string.
NB. Actually DoCmd.RunSQL only works for action queries so would not be appropriate for a SELECT SQL string such as this, but this isn't too relevant to the concept. The point is, however you are using the SQL, you now use strSQL as has already been prepared.

Dates (#)
This is a little more involved so deserves its own thread (Literal DateTimes and Their Delimiters (#).).

Debugging
It's easy to get some of this stuff wrong so I always recommend doing a 'Debug.Print strSQL' before passing the string to the SQL engine when developing the code, or even where you know there is a problem with it somewhere. Use Ctrl-G from the VBA window to show and go to the Immediate Pane where the string is displayed.
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strSQL
  2. DoCmd.RunSQL strSQL
NB. The preceeding example executes only action query SQL, and is not appropriate for some of the earlier example SQL shown. It is intended merely as an illustrative example.
Dec 10 '06 #1
Share this Article
Share on Google+