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.
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 :
It is also possible, in some circumstances, to use the 'other' quote character instead :
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 :
This is straightforward enough with the correct quotes used :
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.) :
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
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.
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.
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.
Code:
Debug.Print Me.ControlName refers to a control on a form. Whereas, Debug.Print "Me.ControlName" simply prints the literal text Me.ControlName
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 :
Code:
strDisp = "Please select ""Bob"" from your list."
Code:
strDisp = "Please select 'Bob' from your list."
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 :
Code:
SELECT * FROM [TableName] WHERE ([AccountName]='Hieronymous')
Code:
strSQL = "SELECT * FROM [TableName] WHERE ([AccountName]='Hieronymous')"
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.) :
Code:
strSQL = "SELECT *" & VbCrLf & _
"FROM [TableName]" & VbCrLf & _
"WHERE ([AccountName]='" & Me.cboAccount & "')"
This string can then be passed to the SQL interpreter
Code:
DoCmd.RunSQL strSQL
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.
Code:
Debug.Print strSQL DoCmd.RunSQL strSQL