在 VBA 中使用 RunSQL 将字符串内容保存到变量的语法(缺少运算符)错误 [英] Syntax (Missing operator) error saving string contents to variable with RunSQL in VBA

问题描述

我正在尝试从文本框中获取选定的文本并将字符串保存到表格中.表中只有一行，其他变量(SelectionStart、SelectionLength)可以毫无问题地保存到表中.

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.

当它到达第三个 RunSQL 命令时，我收到错误:

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

运行时错误3075":查询表达式中的语法错误(缺少运算符)

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

并以所选文本结束.

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

推荐答案

使用一个 UPDATE 语句可以保存任意数量的字段.如果字段是文本类型，请使用撇号分隔符(日期/时间使用 #).我更喜欢 CurrentDb.Execute 来消除弹出警告.

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 将引号和撇号视为特殊字符.如果数据包含引号或撇号字符，一种处理方法是通过将它们加倍来转义"字符，以便 SQL 将它们作为普通文本接受.使用 Replace() 函数:
Replace(Replace(SelectionText, """", """"" & """""), "'", "''")
或者如果这更容易遵循:
Replace(Replace(SelectionText, Chr(34), Chr(34) & Chr(34)), "'", "''")

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)), "'", "''")

另一种方法是使用嵌入参数.TempVars 是在 SQL 字符串中嵌入参数的一种方法.查看 我该怎么做在 Microsoft Access 的不同上下文中使用 VBA 中的参数?

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.

这篇关于在 VBA 中使用 RunSQL 将字符串内容保存到变量的语法(缺少运算符)错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！