MS Access：SQL语句“UPDATE”给出模糊错误3144“UPDATE语句中的语法错误”

I currently am running a form linked to a table where one of the controls displayed in the form is dependent on another another control. Specifically, it is a field called Machine Excavation that needs to be the remaining percentage of a user inputted percentage in the field Hand Excavation. These fields pertain to a specific record chosen specified from another control in the form called Excavation Type. For example if I input 30% into Hand Excavation then Machine Excavation needs to be 70%. I've created a code that will use a RunSQL method in VBA to store this in the underlying table upon execution of an AfterUpdate event in the Hand Excavation control:

我目前正在运行一个链接到表的表单，其中表单中显示的控件之一依赖于另一个控件。具体来说，它是一个名为机器挖掘的领域，需要是现场手动挖掘中用户输入百分比的剩余百分比。这些字段属于从名为Excavation Type的形式的另一个控件中指定的特定记录。例如，如果我输入30％进入手工挖掘，则机器挖掘需要为70％。我创建了一个代码，它将在VBA中使用RunSQL方法在Hand Excavation控件中执行AfterUpdate事件时将其存储在基础表中：

Private Sub Text_HandExcvPerc_AfterUpdate()

DoCmd.RunSQL ("UPDATE tbl-ExcavationType SET [Machine Excavation] = 1 - [Hand Excavation] WHERE [Excavation Type] = Text_ExcvType.Value;") End Sub

tbl-ExcavationType is the table linked to the form and Text_ExcvType.Value is the textbox control that contains the current record identifier. Can someone comment on the syntax of my DoCmd.RunSQL? I keep getting an error.

tbl-ExcavationType是链接到表单的表，Text_ExcvType.Value是包含当前记录标识符的文本框控件。有人可以评论我的DoCmd.RunSQL的语法吗？我一直收到错误。

3 个解决方案

#1

DoCmd.RunSQL ("UPDATE [tbl-ExcavationType] SET [Machine Excavation] = " & _
    "1 - [Hand Excavation] WHERE [Excavation Type] = """ & Text_ExcvType.Value & """;")

Notice that there are three quotation marks surrounding the text box value. Access uses double quotes instead of single quotes for strings. Because you are entering it in a string, you have to use two double quotes to indicate the single double quote for your string, then another one to break the string to concatenate your value. What the system sees will be effectively this:

请注意，文本框值周围有三个引号。 Access使用双引号而不是单引号作为字符串。因为您在字符串中输入它，所以必须使用两个双引号来表示字符串的单引号，然后使用另一个双引号来断开字符串以连接您的值。系统看到的将是有效的：

UPDATE [tbl-ExcavationType] SET [Machine Excavation] = 1 - [Hand Excavation] WHERE [Excavation Type] = "My Data Value";

#2

You have several issues:

你有几个问题：

You need to wrap your table name in brackets because it contains a -.
您需要将表名包装在括号中，因为它包含 - 。
You need to enclose your text values in either single quotes (') or double quotes (").
您需要将文本值括在单引号（'）或双引号（“）中。
You need to add the value of Text_ExcvType.Value rather than the string "Text_ExcvType.Value" into the query.
您需要将Text_ExcvType.Value的值而不是字符串“Text_ExcvType.Value”添加到查询中。

I have tested that the following works:

我测试了以下工作：

Private Sub Text_HandExcvPerc_AfterUpdate()
    DoCmd.RunSQL "UPDATE [tbl-ExcavationType] SET [Machine Excavation] = 1 - [Hand Excavation] WHERE [Excavation Type] = '" & Text_ExcvType.Value "';"
End Sub

#3

What you have here appears to be a calculated field: ie [Machine Excavation] is always 1 - [Hand Excavation] so I don't see why you need to store it. That said you need to replace the words "Text_ExcvType.Value" with the actual value in the control called Text_ExcvType and it can't do that while it's inside speech marks. Try

你在这里看到的是一个计算字段：即[机器挖掘]总是1 - [手工挖掘]所以我不明白为什么你需要存储它。这就是说你需要用名为Text_ExcvType的控件中的实际值替换单词“Text_ExcvType.Value”，并且当它在语音标记内时它不能这样做。尝试

dim strsql as string

strsql = "UPDATE tbl-ExcavationType SET [Machine Excavation] = "

strsql = strsql & "1 - [Hand Excavation] WHERE [Excavation Type] = '"

strsql = strsql & Text_ExcvType.Value & "';" 

DoCmd.RunSQL strsql

This allows access to first evaluate Text_ExcvType.Value, put the result into the string and then execute the sql Doing it this way also allows you to watch the string build in debug to work out where it's going wrong

这允许访问首先评估Text_ExcvType.Value，将结果放入字符串然后执行sql这样做也允许你在调试中观察字符串构建以找出它出错的地方

#1

DoCmd.RunSQL ("UPDATE [tbl-ExcavationType] SET [Machine Excavation] = " & _
    "1 - [Hand Excavation] WHERE [Excavation Type] = """ & Text_ExcvType.Value & """;")

UPDATE [tbl-ExcavationType] SET [Machine Excavation] = 1 - [Hand Excavation] WHERE [Excavation Type] = "My Data Value";

#2