在MS Access中使用SQL在VBA中使用声明的字符串更新查询

Question

已经有一段时间了，所以请原谅任何格式问题。希望用同一表中同一字段中的另一条记录的值更新表字段。

我已经声明了两个字符串，并且strDeal字符串在调试时返回正确的值。但是，当我将字符串引入sql语句时，我无法查询更新字段。我不确定到底是什么地方出了问题，所以请帮助我将不胜感激。

基本知识是我试图根据子查询的返回值在同一表中使用来自同一字段的值更新Case_Qty字段。谢谢!

Dim strDeal As String
Dim strSQL As String
strDeal = DMax("[Deal_No]", "[tblStructuresNoDAworking]", "[Structure_Name] = Forms!frmStructNoDASetup!Structure_Name AND [FG_Ind] = 0 AND [Deal_No] < Forms!frmStructNoDASetup!Deal_No")
strSQL = "UPDATE tblStructuresNoDAworking SET tblStructuresNoDAworking.Case_Qty = (SELECT [Case_Qty] FROM tblStructuresNoDAworking WHERE [Structure_Name] = '" & Me.Structure_Name.Value & "' AND [Deal_No] = '" & strDeal & "') WHERE [Structure_Name] = '" & Me.Structure_Name.Value & "' AND [Deal_No] = '" & Me.Deal_No.Value & "'"
DoCmd.RunSQL (strSQL)

Answer 1

尝试将表单中的值连接在一起:

Dim strDeal As String
Dim strSQL  As String
strDeal = DMax("[Deal_No]", "[tblStructuresNoDAworking]", "[Structure_Name] = '" & Forms!frmStructNoDASetup!Structure_Name & "' AND [FG_Ind] = 0 AND [Deal_No] < '" & Forms!frmStructNoDASetup!Deal_No & "'")
strSQL = "UPDATE tblStructuresNoDAworking " & _
"SET tblStructuresNoDAworking.Case_Qty = " & _ 
"    (SELECT [Case_Qty] " & _
"    FROM tblStructuresNoDAworking " & _
"    WHERE [Structure_Name] = '" & Me.Structure_Name.Value & "' AND [Deal_No] = '" & strDeal & "') " & _
"WHERE [Structure_Name] = '" & Me.Structure_Name.Value & "' AND [Deal_No] = '" & Me.Deal_No.Value & "'"
DoCmd.RunSQL strSQL

对于数字交易:

Dim Deal    As Long
Dim strSQL  As String
Deal = DMax("[Deal_No]", "[tblStructuresNoDAworking]", "[Structure_Name] = '" & Forms!frmStructNoDASetup!Structure_Name & "' AND [FG_Ind] = 0 AND [Deal_No] < '" & Forms!frmStructNoDASetup!Deal_No & "'")
strSQL = "UPDATE tblStructuresNoDAworking " & _
"SET tblStructuresNoDAworking.Case_Qty = " & _ 
"    (SELECT [Case_Qty] " & _
"    FROM tblStructuresNoDAworking " & _
"    WHERE [Structure_Name] = '" & Me.Structure_Name.Value & "' AND [Deal_No] = " & Deal & ") " & _
"WHERE [Structure_Name] = '" & Me.Structure_Name.Value & "' AND [Deal_No] = " & Me.Deal_No.Value & ""
DoCmd.RunSQL strSQL