Syntax (Missing operator) error saving string contents to variable with RunSQL in VBA

Question

I'm trying to take selected text from a text box and save the string to a table. There's only one row to the table, and the other variables (SelectionStart, SelectionLength) are able to save to the table without issue.

When it gets to the third RunSQL command, I get the error:

Run-Time error '3075': Syntax error (missing operator) in query expression

and ends with the selected text.

Sub ArticleTextContentBox_Click()

Dim SelectionStart As String
Dim SelectionLength As String
Dim SelectionText As String

SelectionStart = [Forms]![1CodingArticlesForm]![ArticleTextContentBox].SelStart + 1
SelectionLength = [Forms]![1CodingArticlesForm]![ArticleTextContentBox].SelLength
SelectionText = Mid([Forms]![1CodingArticlesForm]![ArticleTextContentBox], SelectionStart, SelectionLength)

'Runs successfully, to show that SelectionText variable works correctly
MsgBox SelectionText

DoCmd.RunSQL "UPDATE TEMP_StringPosition SET TEMP_StringPosition.StartLocation = " & SelectionStart & ";"
DoCmd.RunSQL "UPDATE TEMP_StringPosition SET TEMP_StringPosition.StringLength = " & SelectionLength & ";"
    
'This is the line that causes the error:
DoCmd.RunSQL "UPDATE TEMP_StringPosition SET TEMP_StringPosition.ExtractedTextChunk = " & SelectionText & ";"
    
End Sub

I'm not sure what I'm missing here as the first two variables are able to update the table without issue

June7 · Accepted Answer · 2020-06-25T20:56:39.483

Use one UPDATE statement to save as many fields as you want. If field is a text type, use apostrophe delimiters (for date/time use #). I prefer CurrentDb.Execute to eliminate popup warnings.

CurrentDb.Execute "UPDATE TEMP_StringPosition SET StartLocation = " & SelectionStart & _ 
   ", StringLength = " & SelectionLength & ", ExtractedTextChunk = '" & SelectionText & "'"

SQL considers quotes and apostrophes as special characters. If data includes quote or apostrophe characters, one way to handle is to 'escape' characters by doubling them so SQL will accept them as normal text. Use Replace() function:
Replace(Replace(SelectionText, """", """" & """"), "'", "''")
Or if this is easier to follow:
Replace(Replace(SelectionText, Chr(34), Chr(34) & Chr(34)), "'", "''")

Another approach is to use embedded parameters. TempVars is one way to embed parameters within SQL string. Review How do I use parameters in VBA in the different contexts in Microsoft Access?

And another is to open a recordset object and use Edit mode to set field to new value.

I tried that code and got the same error when selecting a few lines of text. I think it's because there are quotes in the text itself and that might be confusing access. — Gabe R, Jun 25 '20 at 20:29
Thanks, I ended up up using the recordset object as it was cleaner for me to implement. — Gabe R, Jun 30 '20 at 18:58

score 1 · Answer 2 · answered Jun 25 '20 at 21:48

Consider parameterization that cleanly separates SQL from VBA and avoids the need for string concatenation and quote punctuation and issues of special characters inside strings. MS Access supports parameters with QueryDefs.

SQL (save as a query object)

PARAMETERS prm_loc INT, prm_len INT, prm_txt VARCHAR(255);
UPDATE TEMP_StringPosition sp 
SET sp.StartLocation   = [prm_loc]
    sp.StringLength    = [prm_len]
    ExtractedTextChunk = [prm_txt]

VBA (reference above query)

Sub ArticleTextContentBox_Click()

    Dim SelectionStart, SelectionLength, SelectionText As String
    Dim qdef As QueryDef

    With [Forms]![1CodingArticlesForm]![ArticleTextContentBox]
         SelectionStart = .SelStart + 1
         SelectionLength = .SelLength
         SelectionText = Mid(.Value, SelectionStart, SelectionLength)
    End With

    'Runs successfully, to show that SelectionText variable works correctly
    MsgBox SelectionText

    Set qdef = CurrentDb.QueryDefs("mySavedUpdateQuery")

    ' BIND PARAMETERS
    qdef!prm_loc = SelectionStart
    qdef!prm_len = SelectionLength
    qdef!prm_txt = SelectionText

    ' RUN ACTION
    qdef.Execute dbFailOnError

    Set qdef = Nothing
  End Sub

Syntax (Missing operator) error saving string contents to variable with RunSQL in VBA

2 Answers2

Linked