After much searching and experimenting, I've nearly got DSUM() to work in my query although it isn't applying the criterion to the DSUM (if I remove the optional criteria then the total sum is returned; with the criterion the field stays blank).
My query 'ACTransactionViewQuery' has a column the following DSUM:
DSum("[TxValue]","[ACTransactionViewQuery]","[ACT_TaxDate] <= " &
[ACT_TaxDate]) as Balance
The TXValue is a calculated column, the 'domain' is the same as the current query and I'm wanting to sum transactions before or equal to the current date.
Notwithstanding that I can't get the criteria to work, I have a more general question:
Even if I get it working eventually, I intend using the query as the source for a form and applying a filter. If my limited understanding is correct, I'd expect the DSUM to return the TXValue total for all rows returned by the unfiltered domain rendering the running total useless.
Am I right? If so I'm wasting my time trying to get the query right and might as well resort to a temp. table populated within my form. It is most vexing that it is so difficult to do in a query that which is so simple in a report!
Thanks in advance for any advice or assistance.
I appreciate that this isn't an answer and I'd really like to know how to get this working BUT as the query runs OK without the DSUM criteria I tried filtering the results to see what happens. As expected, the running total includes all rows, not just the filtered ones. As I'll be applying a filter to the query in the form's code, it looks like I've no option but to use a temp. work table in my form. It makes things easier functionally but isn't good for performance. Thanks again anyway.