CODEWITHSUNDEEP
vbams-accessexpression
Jon Brenner
Jon Brenner

Reputation: 1

Microsoft Access Expression - DSum Multiple Criteria - Dates

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: =SUMIFS(current_invoice_amount,campaign_id,A2,invoice_date,"<="&B2) A2 is campaign id and B2 is invoice date. Pretend formula is in C2.

Here's the Access Expression that won't work:

Expr1: DSum("[current_invoice_amount]","[tblAccountingDatabase]","[campaign_id]='" & [campaign_id] & "'" And "[invoice_date]<=#" & [invoice_date] & "#")

Here's an axample of what the table should look like:

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

Upvotes: 0

Views: 8022

Answers (1)

Dale Thompson
Dale Thompson

Reputation: 173

If the [campaign_id] field is a number type, don't put it in single quotes.

If the [invoice_date] field is a date type, don't enclose it in # tags.

Expr1: DSum("[current_invoice_amount]","[tblAccountingDatabase]","[campaign_id]=" & [campaign_id] & " And [invoice_date]<=" & [invoice_date])

Upvotes: 0

Related Questions