unfortunately you can't use an Aggregate query
within an Update query.
you will need to revise the code and use Domain functions:
db.Execute "update tempTable as a " & _
"set a.quantity_impact_day =DMax(""quantity_impact_day"",""theQueryName"",""acctKey="" & a.[acctKey] & ""and secKey ="" & a.secKey & ""asOf = #"" & Format(a.asOf, "mm/dd/yyyy") & "#)"
*note the above code is not tested so you need to tweak it (specially the ").
Can I ask one more (and hopefully last) question?
I made the change to reference a query instead of table and I'm getting and "Compile Error Expected: End of statement" with the QuantityImpact field highlighted (as below)
Here is the new code. All the Query and field names are correct.
db.Execute "update tempTable as a " & "set a.quantityimpact_day = DMax("
quantityimpact","2d_AllOpeningFlowValues","acctKey="& a.[acctKey] & "and secKey =" & a.secKey & "asOf = #" & Format(a.asOf, "mm/dd/yyyy") & "#)"
Here is the original code that references a table
db.Execute "update tempTable as a " inner join Activity_Delta as b " & _
"on a.acctKey = b.acctKey and a.secKey = b.secKey and a.asOf = b.asOf " & _
"set a.quantityimpact_day = b.quantityimpact_day;"
This is where I get hung up... syntax.
As always, thank you for any help