How to fix run-time error '3075' syntax error (missing operator in query expression in a SQL INSERT statement within Microsoft Access?

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
image of error - part 1 How to fix run-time error '3075' syntax error (missing operator in query expression in a SQL INSERT statement within Microsoft Access?

please note attached image that describes the error:

the yellow highlights on line item with Currentdb.Execute sSQL

sSQL = "SELECT [tagID], [usgaapfilesid] FROM [usgaapfileswtags] WHERE [tagID] = " & lTagID & " AND [usgaapfilesid] = " & lIDFileName
      Set rsEnsure = CurrentDb.OpenRecordset(sSQL)
      bNotFoundData = rsEnsure.EOF
      rsEnsure.Close
      Set rsEnsure = Nothing
      
      If bNotFoundData = True Then
       
        sLineItem = Replace(sLineItem, Chr(34), "''")
        
        sSQL = "INSERT INTO [usgaapfileswtags] ([tagID], [usgaapfilesid], [usgaaprow]) VALUES (" & lTagID & ", " & lIDFileName & ", '" & sLineItem & "')"
        CurrentDb.Execute sSQL
        DoEvents
      End If

Open in new window


The following variable's sLineItem  value is the cause of the issue:  
<us-gaap:EarningsPerShareTextBlock id=''ID_6'' contextRef=''FROM_Jan01_2018_TO_Dec31_2018_Entity_0000034088''>&lt;div&gt;&lt;p style='text-align:justify;margin-top:0pt;margin-bottom:0pt;line-height:12pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;margin-left:0pt;' &gt;1&lt;/font&gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;' &gt;2&lt;/font&gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;' &gt;. Earnings &lt;/font&gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;' &gt;Per&lt;/font&gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;' &gt; Share &lt;/font&gt;&lt;/p&gt;&lt;/div&gt;&lt;p style='line-height:20pt;' /&gt;&lt;div&gt;&lt;table style='border-collapse:collapse;' &gt;&lt;tr style='height:11.25pt;' &gt;&lt;td style='width:318pt;border-bottom-style:solid;border-bottom-width:1;text-align:left;vertical-align:bottom;border-color:Black;min-width:318pt;
' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;color:#000000;' &gt;Earnings per common share&lt;/font&gt;&lt;/td&gt;&lt;td style='width:30.75pt;border-bottom-style:solid;border-bottom-width:1;text-align:left;vertical-align:bottom;border-color:Black;min-width:30.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:36.75pt;border-bottom-style:solid;border-bottom-width:1;text-align:right;vertical-align:middle;border-color:Black;min-width:36.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:8pt;font-weight:bold;color:#000000;' &gt;2018&lt;/font&gt;&lt;/td&gt;&lt;td style='width:30.75pt;border-bottom-style:solid;border-bottom-width:1;text-align:right;vertical-align:middle;border-color:Black;min-width:30.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:8pt;font-weight:bold;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:36.75pt;border-bottom-style:solid;
border-bottom-width:1;text-align:right;vertical-align:middle;border-color:Black;min-width:36.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:8pt;font-weight:bold;color:#000000;' &gt;2017&lt;/font&gt;&lt;/td&gt;&lt;td style='width:30.75pt;border-bottom-style:solid;border-bottom-width:1;text-align:right;vertical-align:middle;border-color:Black;min-width:30.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:8pt;font-weight:bold;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:36.75pt;border-bottom-style:solid;border-bottom-width:1;text-align:right;vertical-align:middle;border-color:Black;min-width:36.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:8pt;font-weight:bold;color:#000000;' &gt;2016&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;tr style='height:12.75pt;' &gt;&lt;td style='width:318pt;border-top-style:solid;border-top-width:1;text-align:left;vertical-align:bottom;border-color:Black;min-width:318pt;' &gt;&lt;font style='font-family:Times New Roman;f
ont-size:10pt;font-weight:bold;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:30.75pt;border-top-style:solid;border-top-width:1;text-align:left;vertical-align:bottom;border-color:Black;min-width:30.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:36.75pt;border-top-style:solid;border-top-width:1;text-align:center;vertical-align:middle;border-color:Black;min-width:36.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:30.75pt;border-top-style:solid;border-top-width:1;text-align:center;vertical-align:middle;border-color:Black;min-width:30.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:36.75pt;border-top-style:solid;border-top-width:1;text-align:center;vertical-align:middle;border-color:Black;min-width:36.75pt;' &gt;&lt;font sty
le='font-family:Times New Roman;font-size:10pt;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:30.75pt;border-top-style:solid;border-top-width:1;text-align:center;vertical-align:middle;border-color:Black;min-width:30.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:36.75pt;border-top-style:solid;border-top-width:1;text-align:center;vertical-align:middle;border-color:Black;min-width:36.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;tr style='height:12.75pt;' &gt;&lt;td style='width:318pt;text-align:left;vertical-align:bottom;border-color:Black;min-width:318pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;color:#000000;' &gt;Net income attributable to ExxonMobil&lt;/font&gt;&lt;font style='font-family:Times New Roman;font-size:8pt;color:#000000;' &gt; &lt;/font&gt;&lt;font style='font-family:Times New Roman;font
-size:10pt;font-style:italic;color:#000000;' &gt;(millions of dollars)&lt;/font&gt;&lt;/td&gt;&lt;td style='width:30.75pt;text-align:left;vertical-align:bottom;border-color:Black;min-width:30.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;color:#000000;' &gt;&lt;/font&gt;&lt;/td&gt;&lt;td style='width:36.75pt;text-align:right;vertical-align:bottom;border-color:Black;min-width:36.75pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;color:#000000;' &gt;20

Open in new window


So I need to fix the variable's value in sLineItem in order for the SQL syntax INSERT to work...
What do I need to do to fix it?



I've already attempted to do so by adding the line:  sLineItem = Replace(sLineItem, Chr(34), "''")
Also the data types for usgaaprow is set for LongText and has accepted large values from 5 different files thus far - it's just I'm running into an issue thus far.

Any help would be appreciated!?

Thank you in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Just for testing have you tried manually to insert the sLineItem ...maybe something trivial is the cause (e.g. field size ...is it Long Text ? (memo))

Author

Commented:
Yes the data type is Long Text.  "Also the data types for usgaaprow is set for LongText "
John TsioumprisSoftware & Systems Engineer

Commented:
ok..how about trying to insert it by hand?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yes I was able to copy and paste this manually by hand.  How does this help me solve the 3075 syntax error though?
Paul MacDonaldDirector, Information Systems

Commented:
There seem to be single quotes that have not been affected by your REPLACE command.
John TsioumprisSoftware & Systems Engineer

Commented:
It means that the field can accept the value for sure..
Because it seems the problematic part is not very clear how about executing the code but
1st substitute sLineItem with something simple e.g "Hi"
2nd substitute singel quote (') with chr(39)

Author

Commented:
to your point Paul - it does have inside of it:

table style='border-collapse:collapse;'

which are single quotes.
However I can't just do a replace find with Replace(sLineItem, "'", "''")

cause then it would cause already the double quotes '' with ''''
=====================

the same with John's comment

How do I narrow down and only replace the single quote with chr(39)?? when there are some as '' and others as just '?
John TsioumprisSoftware & Systems Engineer

Commented:
try this : (example)
replace("<us-gaap:EarningsPerShareTextBlock id=''ID_6''","'",""" & chr(39) & """)

Open in new window

Paul MacDonaldDirector, Information Systems
Commented:
I think the problem is that they're not all CHR(34) even if they look like that (and are interpreted that way).   Some may be single quotes and some may be apostrophes (or something like that.  Find the character code for the remaining single quotes to see if it's some other value.  If so, REPLACE that with your double single.
Paul MacDonaldDirector, Information Systems
Commented:
Yeah, there you go.
I added this one line item above and it works now.

Thank you for ya'll help above.

sLineItem = Replace(sLineItem, "'", "''")
sLineItem = Replace(sLineItem, Chr(34), "''")

Open in new window

Author

Commented:
thanks again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial