Insert Into语句中调用DMax函数失效问题求助

你的问题核心是VBA的DMax函数无法识别SQL SELECT语句中定义的别名SKU，原因是两者执行上下文完全分离：

• DMax是VBA环境的函数，在拼接SQL字符串时就会执行，此时SQL里的SKU别名还未被数据库引擎生成，VBA会把[SKU]当成未定义的VBA变量，导致报错。
• 换成具体SKU字符串能运行，是因为直接传入了常量值，不需要依赖SQL的字段别名。

解决方案1：在DMax条件中直接计算SKU表达式 #

既然SKU是由[COMPANY] & "-" & [WHSE] & "-" & [ITEM]生成的，直接把这个表达式放到DMax的条件里，代替对SKU别名的引用：

SQLstr = "Insert Into TEMP_ONHAND Select " & _
    "COMPANY, WHSE, WHSEDESC, ITEM, ITEMDESC, ESTCOSTPRICE, INVONHAND, CONSIGNINVONHAND, " & _
    "QUANTITYINTRANSIT, QTY, COST, LASTINVTRANS, SUPPLIERID, SUPPLIER, " & _
    "BUYER, BUYERNAME, PLANNER, PLANNERNAME, BUYFROMBP, BUYFROMBPNAME, " & _
    "[COMPANY] & '-' & [WHSE] & '-' & [ITEM] AS SKU, " & _
    "Left([WHSEDESC],3) AS TYPE, " & _
    "#" & DMax("[TRANSDATE]", "[TEMP_HISTORY]", "[SKU]='" & [COMPANY] & "-" & [WHSE] & "-" & [ITEM] & "' AND [TRANSTYPE] = 3") & "# AS LAST_RCPT, " & _
    "#1/1/1950# AS LAST_ISSUE, " & _
    "#1/1/2000# AS LAST_ADJUST, " & _
    "'TEST' AS TRAN_RANGE, " & _
    "'TEST' AS ADJ_RANGE " & _
    "From PARTS_ONHAND;"

MsgBox SQLstr
Application.CurrentDb.Execute SQLstr

解决方案2：将DMax嵌入SQL语句（推荐） #

把域聚合函数直接写到SQL里，让数据库引擎处理，这样可以直接基于当前行的字段计算，避免VBA和SQL上下文冲突：

SQLstr = "Insert Into TEMP_ONHAND Select " & _
    "COMPANY, WHSE, WHSEDESC, ITEM, ITEMDESC, ESTCOSTPRICE, INVONHAND, CONSIGNINVONHAND, " & _
    "QUANTITYINTRANSIT, QTY, COST, LASTINVTRANS, SUPPLIERID, SUPPLIER, " & _
    "BUYER, BUYERNAME, PLANNER, PLANNERNAME, BUYFROMBP, BUYFROMBPNAME, " & _
    "[COMPANY] & '-' & [WHSE] & '-' & [ITEM] AS SKU, " & _
    "Left([WHSEDESC],3) AS TYPE, " & _
    "DMax('[TRANSDATE]', '[TEMP_HISTORY]', '[SKU]=''' & [COMPANY] & '-' & [WHSE] & '-' & [ITEM] & ''' AND [TRANSTYPE] = 3') AS LAST_RCPT, " & _
    "#1/1/1950# AS LAST_ISSUE, " & _
    "#1/1/2000# AS LAST_ADJUST, " & _
    "'TEST' AS TRAN_RANGE, " & _
    "'TEST' AS ADJ_RANGE " & _
    "From PARTS_ONHAND;"

MsgBox SQLstr
Application.CurrentDb.Execute SQLstr

注意这里SQL内部的单引号需要用两个单引号转义，确保字符串拼接正确。

这种方法的优势是让数据库引擎统一处理整个查询，性能更优，也避免了VBA和SQL的上下文冲突问题。

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