DMax within Insert Into SQL statement (not working)-CodePudding

I've got a piece of VBA code that pulls data in from a linked table and appends some date columns based on using the DMax function to look up the most recent transaction dates based on SKU and transaction type. I'm getting the following error:

For some reason the DMax function is not seeing the data properly from the SKU field that is in the select statement.

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]='" & [SKU] & "' 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

If I replace the & [SKU] & in the DMax function with an actual text string of a SKU, this code works fine. I'm wondering if the syntax needs to be different somehow within the DMax function in order to reference a field from the select portion of the statement?

CodePudding user response：

Try this:

    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] = '"" & [SKU] & ""' 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;"

CodePudding user response：

The & [SKU] & code is not referencing calculated SKU field of SELECT portion of constructed SQL statement. This is concatenation in VBA, not within the SQL statement. Therefore, VBA looks for [SKU] and can't find anything. In fact, that entire DMax() expression is outside the SQL construction and is trying to execute in VBA. If you want to reference the calculated SKU field as input to the DMax(), the DMax() expression must be embedded in the constructed SQL statement.

Embedding domain aggregate functions with dynamic arguments within SQL constructed in VBA is tricky when text fields are the criteria. Try:
"# & DMax('[TRANSDATE]', '[TEMP_HISTORY]', '[TRANSTYPE] = 3 AND [SKU]=' & Chr(39) & [SKU] & Chr(39)) & # AS LAST_RCPT, " & _

Or build a SELECT query object which includes DMax() function then call that object by the VBA procedure.
SQLstr = "Insert Into TEMP_ONHAND Select * FROM queryName"

Or don't save aggregate data, calculate it when needed.