Creating New Rows

[arnelgp]

check again spcly the 0.

 

[Tskutnik]

Wow so so so great.
1) the date range is perfect.
2) I see some 0's and Nulls in days with no quantity (1000 / IBM0000 for 12/22/14 and 12/23/14), but I can filter those out. I don't want you do do more work than you have to.

Saving me hours upon hours.... TY

If I get stuck I'll shoot back a note.
I hope you can use this logic for another project.

 

[Tskutnik]

I noticed the most recent date created is 4/1/20 for every position, but the activity goes beyond that. I could not find anything in the code with a limit on the Max date, except for a line you commented out, but that does not seem to apply.
Any ideas where I could look?

Actually - I think I found it. There was a hard coded 4/1/20 date. Let me play with it.

Got it now. Today's date - 1, just like I asked for. Ha. Sorry to bother you. I saw more dates int he activity file and forgot the request was max date was Today -1

 

[Tskutnik]

Thanks again for your help. I wrote a load of processes around what you gave me and it is all going well. One question...

in MODULE1 -

I wanted to change the reference to Table [activityTable] to a Query called [Activity_Prep_NetAllDaily]. The field names and types are all the same.
This is the only code that referecnes that Table

db.Execute "update tempTable as a inner join activityTable as b " & _
"on a.acctKey = b.acctKey and a.secKey = b.secKey and a.asOf = b.asOf " & _
"set a.quantity_impact_day = b.quantity_impact_day;"

After a simple find/replace I get the following error
Run-Time error "3017":
Operation must use an updateable query

I copied the Output from [Activity_Prep_NetAllDaily] to a table and and then ran the function after the find/replace - and everything worked correctly, so it is choking on the query, not the data.

Is the code specifically for a Table?

Any help is (always) appreciated.
Thanks very much

 

[arnelgp]

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 ").

 

[Tskutnik]

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 ").

Thanks so much... yet again.
I appreciate your time

 

[Tskutnik]

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

 

[arnelgp]

add another UDF to the code:

Public Function udfLookup(aKey, sKey, aO) As Variant
udfLookup = DLookup("QuantityImpact", "2d_AllOpeningFlowValues", "acctKey=" & aKey & " and secKey=" & sKey & " and AsOf=#" & Format(aO, "mm/dd/yyyy") & "#")
End Function

modify your code to call the UDF above:

CurrentDb.Execute "UPDATE tempTable SET tempTable.quantityImpact_day = udfLookUp(tempTable.[acctKey], tempTable.[secKey], tempTable.[AsOf])"