Subtotals in Form

[Guest]

I have a form that lists the donations history for a donor. I would like to
have 2 subtotals - one to subtotal the donations for the current year and one
to subtotal the donations for the prior year. Do I use Sum Iff? If so, what
is the syntax?

I tried this for the current year subtotal, but it doesn't work:

=Sum(Iif Format([Trans-Check Date], "yyyy")=Format(Now(),"yyyy"), [Amount])

Thanks.
Meryl

 

[Allen Browne]

Try:
=Sum(IIf(Year([Trans-Check Date]) = Year(Date()), [Amount], 0)

Or you could sum the amount directly from the table:
=DSum("Amount", "Donation", "([DonorID] = " & Nz([DonorID], 0) &
") AND ([Trans-Check Date] Between #1/1/" &
Year(Date()) & "# And #12/31/" & Year(Date()) & "#)")

 

[Guest]

Thank you!

Try:
=Sum(IIf(Year([Trans-Check Date]) = Year(Date()), [Amount], 0)

Or you could sum the amount directly from the table:
=DSum("Amount", "Donation", "([DonorID] = " & Nz([DonorID], 0) &
") AND ([Trans-Check Date] Between #1/1/" &
Year(Date()) & "# And #12/31/" & Year(Date()) & "#)")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that lists the donations history for a donor. I would like
to
have 2 subtotals - one to subtotal the donations for the current year and
one
to subtotal the donations for the prior year. Do I use Sum Iff? If so,
what
is the syntax?

I tried this for the current year subtotal, but it doesn't work:

=Sum(Iif Format([Trans-Check Date], "yyyy")=Format(Now(),"yyyy"),
[Amount])

Thanks.
Meryl