473,387 Members | 1,904 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,387 developers and data experts.

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

NeoPa
32,556 Expert Mod 16PB
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
0 80434

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Lionel B | last post by:
Running VC++ 6 under Win2K on i386. I would like to assign a (compile-time) constant that resolves to a quiet NaN (of type double) I can assign a quiet NaN to a *variable* (of type const...
12
by: Sydex | last post by:
When I compile code I get error C2664: 'Integration::qgaus' : cannot convert parameter 1 from 'double (double)' to 'double (__cdecl *)(double)' in this part : double Integration::quad2d(double...
20
by: Anonymous | last post by:
Is there a non-brute force method of doing this? transform() looked likely but had no predefined function object. std::vector<double> src; std::vector<int> dest; ...
3
by: Sascha Herpers | last post by:
Hi, I wrote a c dll with a type library to use it in vb. No problem, everything works fine. Now I needed to pass an array of type double to the dll. I defined the function in the type...
4
by: Michael Mair | last post by:
Hi there, actually, I have posted the same question in g.g.help. As there were no answers, I am still not sure whether this is a bug or only something open to the compiler that is seemingly...
10
by: Robert Palma | last post by:
I'm having trouble figuring out how to pass a pointer to a double array (1 dimensional) to a C function. Declaring array as: double xx; Declaring func. int process( double *input ) Calling...
12
by: Frederik Vanderhaeghe | last post by:
Hi, I have a problem converting text to a double. Why doesn't the code work: If Not (txtdocbedrag.Text = "") Then Select Case ddlBedrag.SelectedIndex Case 0 Case 1
52
by: lcw1964 | last post by:
Greetings, all, I am trying to port a little bit of math code to gcc, that in the original version used the long double version of several functions (in particular, atanl, fabsl, and expl). I...
1
by: JWest46088 | last post by:
I keep getting these error messages: area(double,double) in Rectangle cannot be applied to () return "Area: " + Rectangle.area() + "\tCircumference: " + Rectangle.perimeter(); ...
2
by: Genro | last post by:
#include<stdio.h> #include<TX/graphics.h> #include<time.h> // I need help! struct Krug{ double _x; double _y; double _skox; double _skoy; double...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.