We help IT Professionals succeed at work.
Get Started

Calculating a YTD value in MS ACCESS

2,027 Views
Last Modified: 2012-06-27
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.

Screenshot of Parent/Child Form
Anyone have any suggestions?
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE