Access VBA SQL更新Dynamics 365关联表：Choice列无法更新问题

使用Access结合VBA模块更新关联Dynamics 365的表时，其他字段可正常从源表更新，但单选Choice类型的[Portfolio]列无法更新，目标记录已存在且当前为空值。运行代码时触发运行时错误'3464'：条件表达式中数据类型不匹配，补充：添加WHERE子句过滤空值后，代码可正常运行。

Option Compare Database
Option Explicit
Sub test1()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim strSQLUpdate As String
    
    ' Update the [Portfolio] field in the [Projects Table] table based on the value of the [Portfolio] field in the [Source Data] table.
    strSQLUpdate = "UPDATE [Projects Table] " & _
                    "INNER JOIN [Source Data] ON [Projects Table].[Sub Number] = [Source Data].[Sub Number] " & _
                    "SET [Projects Table].[Portfolio] = " & _
                    "IIf([Source Data].[Portfolio] = 'Lego Projects', '306170000', " & _
                    "IIf([Source Data].[Portfolio] = 'Car Projects', '306170001', " & _
                    "IIf([Source Data].[Portfolio] = 'Cooking Projects', '306170002', " & _
                    "IIf([Source Data].[Portfolio] = 'Arcade Projects', '306170003', " & _
                    "IIf([Source Data].[Portfolio] = 'Pool Projects', '306170004', " & _
                    "IIf([Source Data].[Portfolio] = 'Treehouse Projects', '306170005', " & _
                    "IIf([Source Data].[Portfolio] = 'Other Projects', '306170006', '')))))))" & _
                    "WHERE [SOURCE DATA].[Portfolio] IS NOT NULL;"
    db.Execute strSQLUpdate, dbFailOnError
    
    ' Clean up
    Set db = Nothing
End Sub

错误原因 #

1. 数据类型不匹配：Dynamics 365的Choice类型字段在Access关联表中实际存储为数值类型（Integer/Long），但代码中返回的是字符串格式的选项ID（如'306170000'），导致类型冲突。
2. 空值处理不当：当源表[Portfolio]为空时，IIf返回空字符串''，与目标字段的数值类型不兼容，这是触发错误的核心原因；添加WHERE子句过滤空值后，避免了返回空字符串的场景，因此代码可正常运行。

优化代码 #

Option Compare Database
Option Explicit
Sub test1()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim strSQLUpdate As String
    
    ' 更新[Projects Table]中的[Portfolio]字段，匹配[Source Data]的对应值
    strSQLUpdate = "UPDATE [Projects Table] " & _
                    "INNER JOIN [Source Data] ON [Projects Table].[Sub Number] = [Source Data].[Sub Number] " & _
                    "SET [Projects Table].[Portfolio] = " & _
                    "IIf([Source Data].[Portfolio] = 'Lego Projects', 306170000, " & _
                    "IIf([Source Data].[Portfolio] = 'Car Projects', 306170001, " & _
                    "IIf([Source Data].[Portfolio] = 'Cooking Projects', 306170002, " & _
                    "IIf([Source Data].[Portfolio] = 'Arcade Projects', 306170003, " & _
                    "IIf([Source Data].[Portfolio] = 'Pool Projects', 306170004, " & _
                    "IIf([Source Data].[Portfolio] = 'Treehouse Projects', 306170005, " & _
                    "IIf([Source Data].[Portfolio] = 'Other Projects', 306170006, Null)))))))" & _
                    "WHERE [SOURCE DATA].[Portfolio] IS NOT NULL;"
    db.Execute strSQLUpdate, dbFailOnError
    
    ' 清理对象
    Set db = Nothing
End Sub

额外优化建议 #

• 用Switch函数替代多层嵌套IIf，提升SQL可读性：

  Switch(
      [Source Data].[Portfolio] = 'Lego Projects', 306170000,
      [Source Data].[Portfolio] = 'Car Projects', 306170001,
      [Source Data].[Portfolio] = 'Cooking Projects', 306170002,
      [Source Data].[Portfolio] = 'Arcade Projects', 306170003,
      [Source Data].[Portfolio] = 'Pool Projects', 306170004,
      [Source Data].[Portfolio] = 'Treehouse Projects', 306170005,
      [Source Data].[Portfolio] = 'Other Projects', 306170006,
      True, Null
  )
  

• 确认[Projects Table]的[Portfolio]字段数据类型与Dynamics 365中Choice字段的存储类型一致（通常为Long Integer）。

内容的提问来源于stack exchange，提问作者BaronTruth