Problem with single quote

[learning_codes]

Hi,

I am able to run the queries without quote but not has the quote. I
hope you can help me to solve this for me.

Product Name has the single quote like: John Simth's

I'm not able to run the query for John's Smith's but all other queries
works for without quote.


The error message comes from the line of code: (([Order].ProductName)=
"' & strProduct & '"))

Here is the code:


***********************************************************
strTemp_Product = dbs.TableDefs(0).Name

strSQL_Product = "SELECT [ProductDescriptions]
FROM [Order]
WHERE ((([Order].[ProductGroup]) = 'Outdoor') AND
(([Order].ProductName)= '" & strProduct & "'))
ORDER BY [Order].ProductName;"

strTemp_Product = dbs.TableDefs(1).Name

Set qdf_Product = dbs_Product.CreateQueryDef(strQName_Product,
strSQL_Product)

qdf_Product.Close

**************************************************************************

Your help would be much appreciated.
Thanks

 

[Clifford Bass]

Hi,

You need to double-up any apostrophes (') or quotes (") within the
quoted string, depending on which you are using for to delimit the string.
In your case, since you are using apostrophes as the string delimiter, you
would change you code to:

(([Order].ProductName)= "' & Replace(strProduct, "'", "''") & '"))

The Replace() function replaces all occurances of the second string
with the third string within the first string. The second parameter is "
followed by ' followed by ". The third is ", ', ', ". It is good policy to
always do this when dealing with strings since you never can tell when some
piece of string data will have one of the string delimiters within it.

Clifford Bass

 

[Ken Snell \(MVP\)]

Use Replace function to double-up the ' character:

strSQL_Product = "SELECT [ProductDescriptions]
FROM [Order]
WHERE ((([Order].[ProductGroup]) = 'Outdoor') AND
(([Order].ProductName)= '" & Replace(strProduct, "'","''",1, -1, 1) & "'))
ORDER BY [Order].ProductName;"

 

[John W. Vinson]

Hi,

I am able to run the queries without quote but not has the quote. I
hope you can help me to solve this for me.

Product Name has the single quote like: John Simth's

I'm not able to run the query for John's Smith's but all other queries
works for without quote.

You can use a double singlequote, or delimit the criterion with double
doublequotes (how's THAT for doubletalk!)

Try

The error message comes from the line of code: (([Order].ProductName)= """ &
strProduct & """"))

That's three doublequotes before the variable and four after. This works
because including two consecutive doublequotes in a string delimited by
doublequotes inserts just one doublequote; and you can use " to delimit a
criterion containing ' marks:

(([Order].ProductName) = "John Smith's"))

will work, but what you're getting is

(([Order].ProductName) = 'John Smith's'))

The apostrophe in the name is being seen as the closing quote for the criteria
string, and the remaining " 's " is causing the entire criterion to be
ill-formed.