Adding an AND in Control Source

[cj_steve]

Hello,

I have a field in a report (Combined Dues Ending) that reads:

=IIf(DSum("[Amt of Dues]","City","[Amt Paid]"=0") Is Null,0,
DSum("[Amt of Dues]","City","[Amt Paid]=0"))

where Amt of Dues and Amt Paid are in the City Table.
I need to add another condition the to the Amt Paid = 0. One that should read ANd where Date (located in City Table) is <= the Select_Date located on the same Report.

I get syntax errors on the below code.

=IIf(DSum("[Amt of Dues]","City","[Amt Paid]"=0"AND "[City]![Date Pd]<=" & "[Reports]![Combined Dues Ending]![Select_Date]") Is Null,0,
DSum("[Amt of Dues]","City","[Amt Paid]=0" AND "[City]![Date Pd]<=" & "[Reports]![Combined Dues Ending]![Select_Date]" ))

 

[cj_steve]

Ok, I have fixed the syntax errors; however it now doesn't seem to be paying any attention to the where clauses, it just gives the sum of Amt of Dues irregardless of the Date and Amt Paid = 0 restrictions.

=IIf(DSum("[Amt of Dues]","City","[Amt Paid]=0" And "[Date Pd]<=" & "[Reports]![Combined Dues Ending]![Select_Date]") Is Null,0,
DSum("[Amt of Dues]","City","[Amt Paid]=0" And "[Date Pd]<=" & "[Reports]![Combined Dues Ending]![Select_Date]"))

Any help would be much appreciated. Thank you.

 

[cj_steve]

Some sql code might look like


Select Sum(Amt of Dues)
From City
Where Amt Paid = 0 AND
Date Pd <= :Select_date;

Thanks

 

[cj_steve]

Thanks, the Nz function is much better.

However, I now get #Error where I should have the sum. I am using the following equation

=Nz(DSum("[Amt of Dues]","City","[Amt Paid] = 0 And [Date Pd] <= #" & [Reports]![Combined Dues Ending]![Select_Date] & "#"),0)

Thanks