如何解决Access设计视图中SQL连接表达式报错问题

你的问题核心是Access设计视图不支持在INNER JOIN的ON子句中使用字段拼接的表达式（比如tblALL.fldMixLoadNr_2 & tblALL.fldMLCN = tblCommonB.B），但SQL视图运行正常。下面提供两种能实现相同功能且兼容设计视图的写法：

方法一：用子查询提前生成拼接字段 #

先通过子查询把tblALL中需要拼接的字段计算好，再和tblCommonB做等值连接，设计视图可以识别这种常规的字段关联：

INSERT INTO DB_ADGB ( ADGID, AID, BID, IsLQ )
SELECT subALL.fldDgmId AS ADGID, NAAs.AID, tblCommonB.ID AS BID, False AS IsLQ
FROM NAAs, 
(SELECT fldDgmId, fldMixLoadNr_2 & fldMLCN AS CombinedB, fldMLCN FROM tblALL) AS subALL
INNER JOIN tblCommonB 
ON subALL.CombinedB = tblCommonB.B
WHERE NAAs.ATypeId = 8 AND subALL.fldMLCN <> "OK"
ORDER BY subALL.fldDgmId;

方法二：将拼接条件移至WHERE子句 #

把原来的INNER JOIN逻辑改成多表交叉连接，再在WHERE里添加拼接匹配条件，这种写法也能被设计视图正确识别：

INSERT INTO DB_ADGB ( ADGID, AID, BID, IsLQ )
SELECT tblALL.fldDgmId AS ADGID, NAAs.AID, tblCommonB.ID AS BID, False AS IsLQ
FROM NAAs, tblALL, tblCommonB
WHERE NAAs.ATypeId = 8 
  AND tblALL.fldMLCN <> "OK"
  AND tblALL.fldMixLoadNr_2 & tblALL.fldMLCN = tblCommonB.B
ORDER BY tblALL.fldDgmId;

两种写法都能实现你要的功能：当tblCommonB的B字段等于tblALL中fldMixLoadNr_2和fldMLCN的拼接值时，关联获取对应字段数据并插入目标表。

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