I am trying to calculate a YTD value on a Subform (Datasheet View).
I have two tables, tblPaychecks and tblDeductions. tblDeductions is a child of tblPaychecks... tblDeductions.Paycheck_ID = tblPaychecks.ID
I am trying to display a form that shows the Paycheck details on top, and at the bottom a Datasheet view of all the deductions on the check being viewed. So far, this portion all works perfectly. The problem is, I also would like to add a calculated column to the Deductions Subform that shows a sum of all the deductions for the specified "deduction type" in the current fiscal year. I've tried using Domain based lookups, but ended up hitting a "string size limit" when defineing the ControlSource code. I then though of trying to build the code using VBA, but because of the way Form Events fire, i can't seem to get the values I need.
Attached is an image of my form with sample data. For each "Deduction" row I need to calculate the YTD for the Fiscal and Calendar year. The code should be the same except for the "Year Range" filter, so if I can get one to work, the other should be the same logic.
Anyone have any suggestions?