DSUM report textbox control source with multiple criteria

Question

I cannot get the following DSUM to work.  It failed with the addition of the date criteria.  no error, just null.  can someone tell me what is wrong with my criteria syntax?

=-DSum("[amount_amt]","Sales","[account_id]=145 AND [transaction_date_h]=Year(Now()) and [transaction_date_h]=Month(Now())")

Answer 1

You need to have the same level of granularity on both sides of the "=". Try:

=-DSum("[amount_amt]","Sales","[account_id]=145 AND Year([transaction_date_h])=Year(Now()) and Month([transaction_date_h])=Month(Now())")

I believe you could also use

=-DSum("[amount_amt]","Sales","[account_id]=145 AND Format([transaction_date_h],'YYYYMM')=Format(Date(),'YYYYMM')")

If the record source of the report is "Sales" you should be able to use something like:

=Sum([amount_amt] *([account_id]=145 AND Format([transaction_date_h],"YYYYMM")=Format(Date(),"YYYYMM") ) )