Link to home
Create AccountLog in
Avatar of al4629740
al4629740Flag for United States of America

asked on

Query syntax error with quotation marks in the data

I am adjusting a VB6 statement in which the application is making a request from the SQL database and the value contains quotation marks in it.  The result is an error:  Incorrect syntax near 'value'  

The requesting statement from VB6 is:

select count(*), Max(Grouping) from tblOrgHours where Narrative = '" & Text1 & "'"

If Text1 contains quotation marks in it then the error is produced.  How can I avoid this?
Avatar of aikimark
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Const QUOTE = """"

select count(*), Max(Grouping) from tblOrgHours where Narrative = '" & Te" & QUOTE & "xt1 & "'"

Open in new window

Avatar of al4629740


I am so sorry I made an error in my statement on the original question.

Text1 is a paragraph field on the VB6 form.  It sometimes contains quotation marks within the paragraph. Quotation marks are also found in the Narrative field in the database.  I am doing a comparison with the SQL statement to see if anything from the form(Text1) exists in the database(Narrative)  When executing the sql command thats when I get the error.

How do I resolve this problem?  Sorry for the poor handling of my question.  Please feel free to ask for any other clarification but I hope that clarifys it.

I believe the quotation marks that are causing the error look like this in the narrative: 

Bobby when to his friend's house 


He said it was "unsuitable"

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Apostrophe characters shouldn't be a problem.

Did you try my statement?
BTW those Chr numbers come from places like this.
If your string contains quote characters, DO NOT change 34 to 39 (per Martin)

I just tested my issue and its definitely scenarios where the paragraph in Text1 has an apostrophe.  

ie.  The dog ran to Barry's house  


The 'dog' has some issues

Both of these produce errors

I don't believe either of your answers will work.  You see I'm trying to see if this narrative from Text1 exists in the database so if I replace the apostrophe in Text1 with another character, then it won't properly locate the paragraph since the value has essentially changed.  Ugh!

What is the text of the matching item in the database?
Since your Text1 field contains apostrophe characters and not quote characters, then follow Martin's comment and change the 34 values to 39 values in my statement.

by golly jee whiz.  I should have just shut up and tried the solution.  Yes.  Martin was right.  change aikmarks solution to 39!

I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018