Microsoft Access Expression - DSum Multiple Criteria - Dates
<p>I'm trying to write a DSum expression with multiple criteria, but can't seem to get it right. Not totally Access-savvy, but pretty sure I have the syntax correct. The result I get with only one criteria is correct. When I add a second, the expression ignores both criteria, and gives me a sum for the entire column. Any idea what I'm doing wrong??? The goal is to have a "rolling total" for each campaign, so each time a new invoice is sent it is added to this rolling total field. Best excel equivalent formula: <code>=SUMIFS(current_invoice_amount,campaign_id,A2,invoice_date,"&lt;="&amp;B2)    A2 is campaign id and B2 is invoice date. Pretend formula is in C2.</code> </p>

<p>Here's the Access Expression that won't work:</p>

<pre><code>Expr1: DSum("[current_invoice_amount]","[tblAccountingDatabase]","[campaign_id]='" &amp; [campaign_id] &amp; "'" And "[invoice_date]&lt;=#" &amp; [invoice_date] &amp; "#")
</code></pre>

<p>Here's an axample of what the table should look like:</p>

<pre><code>campaign   invoice_date   current_invoice_amount   rolling_total
a             1/1/16                5                    5
a             2/1/16                6                    11
a             3/1/16                4                    15
b             1/1/16                4                    4
b             2/1/16                4                    8
c             1/1/16                6                    6
</code></pre>