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.
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.
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.
strDisp = "Please select ""Bob"" from your list."
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.
In MS Access though, we come across the situation where we need to use strings that include embedded strings. Particularly with SQL code.
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.
"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.
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.
This is a little more involved so deserves its own thread (Literal DateTimes and Their Delimiters (#).).
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.
Compile-time constant quiet NaN (double) ???