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
- 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 :
Expand|Select|Wrap|Line Numbers
- strDisp = "Please select ""Bob"" from your list."
Expand|Select|Wrap|Line Numbers
- 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 :
Expand|Select|Wrap|Line Numbers
- SELECT * FROM [TableName] WHERE ([AccountName]='Hieronymous')
Expand|Select|Wrap|Line Numbers
- 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.) :
Expand|Select|Wrap|Line Numbers
- strSQL = "SELECT *" & VbCrLf & _
- "FROM [TableName]" & VbCrLf & _
- "WHERE ([AccountName]='" & Me.cboAccount & "')"
This string can then be passed to the SQL interpreter
Expand|Select|Wrap|Line Numbers
- 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.
Expand|Select|Wrap|Line Numbers
- Debug.Print strSQL
- DoCmd.RunSQL strSQL