Single vs. double quotes

[BruceM]

In something such as a WHERE condtion for opening a form, if the matching
field (EmployeeID in this case) is a text field, the WHERE condtion may be
something like:

Dim strWhere as string
strWhere = "EmployeeID = " " " & Me.EmployeeID & " " " "
DoCmd.OpenForm "frmMain", , , strWhere

However, the strWhere line could also be:
strWhere = "EmployeeID = ' " & Me.EmployeeID & " ' "

As I understand, the first would be the choice if EmployeeID could contain
an apostrophe. The second would be the choice if there could be a double
quote in EmployeeID. Other than these circumstances, is there a reason to
prefer one approach over the other, or limitations in either case?

Also, in the unlikely event that the selected field could contain both
apostrophes and double quotes, I think the approach is to double the single
quotes:

strWhere = "EmployeeID = ' ' " & Me.EmployeeID & " ' ' "

or to replace each single quote in the above with a pair of double quotes:
strWhere = "EmployeeID = " " " " " & Me.EmployeeID & " " " " " "

Again, is there a place for either of these approaches other than to guard
against text that could contain both apostrophes and single quotes?

I realize that EmployeeID is not likely to contain either single or double
quotes, and almost certainly not both, but I can imagine fields where the
conditions could apply. The question is general, and not about a particular
field in an actual database.

 

[Stefan Hoffmann]

hi Bruce,

In something such as a WHERE condtion for opening a form, if the matching
field (EmployeeID in this case) is a text field, the WHERE condtion may be
something like: [...]
The question is general, and not about a particular
field in an actual database.

I use the single quote as inline string delimiter. With this global function

Public Function SQLEscape(AString As String, _
Optional ADelimiter As String = "'" _
) As String

SQLEscape = Replace(AString, ADelimiter, ADelimiter & ADelimiter)

End Function

I have no problems with strings anymore. Just use it always for string
arguments like that:

strWhere = "EmployeeID = '" & SQLEscape(Me.EmployeeID) & "' "

Sometime I use it in a more comfortable manner:


Public Function SQLQuote(AString As String
) As String

Const Delimiter As String = "'"

SQLQuote = Delimiter & _
Replace(AString, Delimiter, Delimiter & Delimiter) & _
Delimiter

End Function

and

strWhere = "EmployeeID = " & SQLQuote(Me.EmployeeID)


mfG
--> stefan <--

 

[BruceM]

Stefan,

Thanks for the reply. As I understand it, if AString contains an apostrope
(single quote), it is replaced with two apostrophes. The difference between
SQLEscape and SQLQuote seems to be that in the SQLEscape the delimiter is
included with the name of the function, while in SQLQuote it is a separate
constant.

Replace(AString, Delimiter, Delimiter & Delimiter)

If AString is, say, O'Leary, it becomes O' 'Leary. If there is a double
quote, no problem since the delimiter is the single quote. The entire
SQLQuote, then, becomes:
'O' 'Leary'
If AString is Hoffman, it is rendered as: 'Hoffman'
Do I understand correctly?

hi Bruce,

In something such as a WHERE condtion for opening a form, if the matching
field (EmployeeID in this case) is a text field, the WHERE condtion may
be something like: [...]
The question is general, and not about a particular field in an actual
database.

I use the single quote as inline string delimiter. With this global
function

Public Function SQLEscape(AString As String, _
Optional ADelimiter As String = "'" _
) As String

SQLEscape = Replace(AString, ADelimiter, ADelimiter & ADelimiter)

End Function

I have no problems with strings anymore. Just use it always for string
arguments like that:

strWhere = "EmployeeID = '" & SQLEscape(Me.EmployeeID) & "' "

Sometime I use it in a more comfortable manner:


Public Function SQLQuote(AString As String
) As String

Const Delimiter As String = "'"

SQLQuote = Delimiter & _
Replace(AString, Delimiter, Delimiter & Delimiter) & _
Delimiter

End Function

and

strWhere = "EmployeeID = " & SQLQuote(Me.EmployeeID)


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Bruce,

Thanks for the reply. As I understand it, if AString contains an apostrope
(single quote), it is replaced with two apostrophes. Yup.

The difference between
SQLEscape and SQLQuote seems to be that in the SQLEscape the delimiter is
included with the name of the function, while in SQLQuote it is a separate
constant.

Nope.

Using SQLEscape you can define the delimiter yourself, but it as the
single quote as default. So you can do:

SQL = "Field = """ & SQLEscape(StringValue, """") & """"

If you like to use the double quote.

Replace(AString, Delimiter, Delimiter & Delimiter)
If AString is, say, O'Leary, it becomes O' 'Leary. If there is a double

No space: O'Leary becomes O''Leary.

If AString is Hoffman, it is rendered as: 'Hoffman'
Do I understand correctly?

Yup.


mfG
--> stefan <--

 

[BruceM]

Thanks again for the reply. I get it now about how the delimiter is defined
in SQLEscape.

I should have mentioned that I added the space between the single quotes for
clarity. I realize that the space is not part of the string.

I like how these functions eliminate the complications that can arise if
there are single or double quotes in the text string. I'm going to make
them part of my arsenal. Thanks very much for providing them.