Calculated Field on Form To return YTD totals

[maxmaggot]

Hi folks,
I have a calculated field on an MS access 2010 form and I am trying to calculate the YTD total. I have generated the following SQL for this field



= SELECT Sum(Donations.Amount) AS SumOfDonations
FROM Donations
GROUP BY DatePart("yyyy",[DatePaid])
HAVING (((DatePart("yyyy",[DatePaid]))=DatePart("yyyy",Date())));

I keep getting a syntax error about the subquery parenthesis and I cannot figure it out.

Any help?

[pbaldy]

You can't have SQL as the control source. Try a DSum().

[maxmaggot]

Thanks Paul. Just figured that out. Created a query to calculate the answer which looks like

Field: Amount
Table: Donations
Total: Sum

Field: DatePart("yyyy",[DatePaid])
Total: Group By



then used

=DLookUp("[SumOfAmount]","[Donations - YTD Amount Total]")

in the calculated field

[pbaldy]

Happy to help, though I would have avoided the extra query and used DSum(). Welcome to the site by the way!

[maxmaggot]

I've said to myself I should do this properly from the start and tried using DSUM(). I'm getting a #Type error.

=DSum("[Amount]","[Donations]") & DLookUp(DatePart("yyyy","[DatePaid]"),"[Donations]",DatePart("yyyy",Date()))

Thanks for your time

[pbaldy]

It would be a single formula. See if this helps:

http://access.mvps.org/access/general/gen0018.htm

if not, can you post the db here?