finding quotation marks in a string

  • Thread starter Thread starter eric
  • Start date Start date
E

eric

I am trying to find the occurances of double quotation
marks in a string field description where the marks are
used to describe inches. There is a syntax error with
the Instr() function.

strQuote = Chr$(34)
str = "SELECT [PO Parts].Flag FROM [PO Parts] " & _
"WHERE (((InStr(0,[PO Parts].[Description], """ &
strQuote & """))>0));"
Set rst = db.OpenRecordset(str, dbOpenSnapshot)
 
Your problem is that you can't use double quotes because
you are using them to enclose the characters you want to
make up the variable str. It has been my experience that
when building a string to create an SQL query, you need
to use single quotes in the SQL query itself. Try this:

strQuote = Chr$(34)
str = "SELECT [PO Parts].Flag FROM [PO Parts] " & _
"WHERE (((InStr(0,[PO Parts].[Description], '" &
strQuote & "'))>0));"
Set rst = db.OpenRecordset(str, dbOpenSnapshot)

However, I like this better:

strQuote = Chr$(34)
str = "SELECT [PO Parts].Flag FROM [PO Parts] " & _
"WHERE ((([PO Parts].[Description]) like '*' &
strQuote));"
Set rst = db.OpenRecordset(str, dbOpenSnapshot)

And this even better:

str = "SELECT [PO Parts].Flag FROM [PO Parts] " & _
"WHERE ((([PO Parts].[Description]) like '*' & Chr(34)));"
Set rst = db.OpenRecordset(str, dbOpenSnapshot)

I hope I got the parentheses correct.

Joe
 
str = "SELECT [PO Parts].Flag FROM [PO Parts] " & _
"WHERE (((InStr(0,[PO Parts].[Description], """ &
strQuote & """))>0));"
Click to expand...

LIKE is probably easier to use than Instr():-

strSQL = "SELECT Flag " & _
"FROM [PO Parts] " & _
"WHERE Description LIKE ""*" & strQuote & "*"";"

' and give yourself a chance to see what is actually being sent...
Debug.Print strSQL


If strQuote is likely to contain a dquote character itself, then it needs
to be doubled up like

"...LIKE ""*" & Replace(strQuote, """","""""") & "*"";"

Hope that helps


Tim F
 
I am trying to find the occurances of double quotation
marks in a string field description where the marks are
used to describe inches. There is a syntax error with
the Instr() function.

strQuote = Chr$(34)
str = "SELECT [PO Parts].Flag FROM [PO Parts] " & _
"WHERE (((InStr(0,[PO Parts].[Description], """ &
strQuote & """))>0));"
Set rst = db.OpenRecordset(str, dbOpenSnapshot)
Click to expand...

Try:

str = "SELECT [PO Parts].Flag FROM [PO Parts] " & _
"WHERE [Description] LIKE '*""*';"

Two consecutive doublequotes within a doublequoted string are treated
as a single doublequote (how's that for doubletalk!), and you can use
' as a delimiter for the LIKE string.
 
Back
Top