Strings With Quotation Marks

[Joe]

Hi,

I need to write a query with a string field in the WHERE
clause. I am running into problems when the value in the field
contains a quotation mark. For example: "Men's Wear"

sWhereClause = "[NAICS]='" & cboTitle.Value & "'"

can anyone tell me how to write my WHERE clause so that the query
doesn't choke on string values that contain a quote mark (apostrophe)?


Thanks,
J

 

[Dirk Goldgar]

in message

Hi,

I need to write a query with a string field in the WHERE
clause. I am running into problems when the value in the field
contains a quotation mark. For example: "Men's Wear"

sWhereClause = "[NAICS]='" & cboTitle.Value & "'"

can anyone tell me how to write my WHERE clause so that the query
doesn't choke on string values that contain a quote mark (apostrophe)?

You can do this in several ways.

1. By using the double-quote character ("), as is returned by the function
Chr(34), to surround the string:

sWhereClause = _
"[NAICS]=" & Chr(34) & cboTitle.Value & Chr(34)

which yields this value for sWhereClause:

[NAICS]="Men's Wear"

2. By doubling up the embedded quote:

sWhereClause = _
"[NAICS]='" & Replace(cboTitle.Value, "'", "''") & "'"

which yields this value for sWhereClause:

[NAICS]='Men''s Wear'

(which will be interpreted properly).

3. In most problem domains, a string is more likely to contain the
single-quote than the double-quote. To cover all contingencies, I tend to
use code like this:

Const Q As String = """"
Const QQ = Q & Q

sWhereClause = _
"[NAICS]=" & Q & Replace(cboTitle.Value, Q, QQ) & Q

which yields this value for given your example value for cboTitle:

[NAICS]="Men's Wear"

and yields this value for a value of 'James "Jimmy" Jones':

[NAICS]="James ""Jimmy"" Jones"

(which will be interpreted properly).

 

[Roger Converse]

Hi Joe,

Something like this should work.

""" & [Forms]![frmName]![cboTitle] & """

Thanks,
Roger

 

[Krzysztof Pozorek [MVP]]

(...)

3. In most problem domains, a string is more likely to contain the
single-quote than the double-quote. To cover all contingencies, I tend to
use code like this:

Const Q As String = """"
Const QQ = Q & Q

sWhereClause = _
"[NAICS]=" & Q & Replace(cboTitle.Value, Q, QQ) & Q

which yields this value for given your example value for cboTitle:

[NAICS]="Men's Wear"

and yields this value for a value of 'James "Jimmy" Jones':

[NAICS]="James ""Jimmy"" Jones"

(which will be interpreted properly).

OK, nice. And you can also write:

sWhereClause = "NAICS=Eval('Forms!Form1!cboTitle')"

This will be well interpreted also, for these all combinations of:
Men's Wear
A'b" c' "d
A's''d''lk"""&(*('@#
....

K.P.
www.access.vis.pl

 

[Dirk Goldgar]

And you can also write:

sWhereClause = "NAICS=Eval('Forms!Form1!cboTitle')"

This will be well interpreted also, for these all combinations of:
Men's Wear
A'b" c' "d
A's''d''lk"""&(*('@#

Nice one! I don't think I've ever seen that before, but of course it will
work (so long as the query is being run in an Access environment).

If the query is being run by Access, rather than by DAO directly, you don't
have to invoke the Eval function. For example,

DoCmd.OpenForm "Form1", _
WhereCondition:="NAICS=Forms!Form1!cboTitle"