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]" ))

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.

Some sql code might look like


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

Thanks

You still don't have the quotes in the correct places and you can use the Nz() function which makes for a less complicated expression:

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

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

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

Use the just the field name when referring to a field in the report's recordsource. Also make sure that the control name of the control holding this expression has a name different from the name of any field in the recordsource.