msaccess vba的位置

Question

添加where语句时，为什么以下msaccess vba语句失败？我想检查字段是否有值

Set rsMySet = CurrentDb.OpenRecordset("PivotTblInvDepts")

'Start the count at the position number of the first column-oriented field
'Remember that Recordsets start at 0
    For i = 3 To rsMySet.Fields.Count - 1
'*********************************************************************************************************************
'Use the recordset field.name property to build out the SQL string for the current field
    strSQL = "INSERT INTO TabularDepts ([Depts], [Code], [Description], [Qty])" & _
    "SELECT" & "'" & rsMySet.Fields(i).Name & "'" & " AS Dept," & _
    "[PivotTblInvDepts].[Code],[PivotTblInvDepts].[Description]," & _
    "[" & rsMySet.Fields(i).Name & "]" & _
    "FROM PivotTblInvDepts;" & _
    "WHERE" & " (rsMySet.Fields(i).Name)>0"

Answer 1

尝试删除FROM PivotTblInvDepts;中的;。

此外，您需要在前一行的末尾留出空格。"[" & rsMySet.Fields(i).Name & "]" & _应为"[" & rsMySet.Fields(i).Name & "] " & _。同样，一定要添加一个空格，这样就不会产生FROM PivotTblInvDeptsWHERE

您当前的SQL代码如下所示

INSERT INTO TabularDepts ([Depts], [Code], [Description], [Qty])
SELECT '<data>' As Dept,
[PivotTblInvDepts].[Code],[PivotTblInvDepts].[Description],
[<data>]
FROM PivotTblInvDepts;
WHERE <condition>

移除;后，插入件将更加整洁。