Problem with single quote

  • Thread starter Thread starter learning_codes
  • Start date Start date
L

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
 
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
 
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;"
 
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.
Click to expand...

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.
 
Back
Top