Running total field

[Michael S.]

I have built an education assistance database for tuition reimburstments. The
running total field I have on the data entry form needs to reflect how much
each employee has received in the current calendar year.

The school information, semester and dollars are in a sub-form of the data
entry form for employee information. I have a running total control on the
employee form that pulls from the subform school, however I can not get it to
calculate only between dates of 1.1.2008 and 12.31.2008.

Any help will be appricated.

Thank you
Michael S.

 

[John W. Vinson]

I have built an education assistance database for tuition reimburstments. The
running total field I have on the data entry form needs to reflect how much
each employee has received in the current calendar year.

The school information, semester and dollars are in a sub-form of the data
entry form for employee information. I have a running total control on the
employee form that pulls from the subform school, however I can not get it to
calculate only between dates of 1.1.2008 and 12.31.2008.

Um?

That's not the current calendar year.

What's the logic here? What's the Recordsource for the subform? What's the
Control Source of the "running total control"?

For year to date, you could use

=DSum("[reimbursement]", "[SomeTableName]", "[EmployeeID] = " &
[txtEmployeeID] & " AND [ReimbursementDate] BETWEEN DateSerial(Year(Date()),
1, 1) AND Date")

using your own table and fieldnames of course.

John W. Vinson [MVP]

 

[Michael S.]

I have built an education assistance database for tuition reimburstments. The
running total field I have on the data entry form needs to reflect how much
each employee has received in the current calendar year.

The school information, semester and dollars are in a sub-form of the data
entry form for employee information. I have a running total control on the
employee form that pulls from the subform school, however I can not get it to
calculate only between dates of 1.1.2008 and 12.31.2008.

Um?

That's not the current calendar year.

What's the logic here? What's the Recordsource for the subform? What's the
Control Source of the "running total control"?

For year to date, you could use

=DSum("[reimbursement]", "[SomeTableName]", "[EmployeeID] = " &
[txtEmployeeID] & " AND [ReimbursementDate] BETWEEN DateSerial(Year(Date()),
1, 1) AND Date")

using your own table and fieldnames of course.

John W. Vinson [MVP]

Hi John,

I know that is not the correct calendar year However, as this year is
over and we are inputting applications for the 2008 calendar year I need to
have a running total that way the person entering the data can see when an
employee reaches the $3000 limit.

I have a query built to show only dates between 1.01.08 and 12.31.08. This
shows only those records with a dollar amount for that time frame. Where I am
stuck is getting that to subtotal and display into another control.

Hope that makes this a little more understandable.

Thanks

 

[John W. Vinson]

I know that is not the correct calendar year However, as this year is
over and we are inputting applications for the 2008 calendar year I need to
have a running total that way the person entering the data can see when an
employee reaches the $3000 limit.

I have a query built to show only dates between 1.01.08 and 12.31.08. This
shows only those records with a dollar amount for that time frame. Where I am
stuck is getting that to subtotal and display into another control.

No, it doesn't. Sorry.

You can change the criteria on my suggested DSum() to the 2008 dates rather
than the DateSerial and Date expressions.

What are these two controls? Why have the same sum shown in both? What are the
Control Sources of the two controls? What do you mean by "getting that to
subtotal"?


John W. Vinson [MVP]

 

[Michael S.]

Hmmm

Control Source for the running total text box =[Forms]![School
subform]![RunningTotal]

Control Source for the Semester Total text box named RunningTotal
=Sum([SemesterDollarAmount])

Ok let me see if I can explain this better.
Each employee that is entered into the database will have multiple recrods,
1 record for each class taken. Each class has a cost. The classes are listed
in a subform with the school information and the cost of the class. What I am
trying to do is calculate a running total on the main form. This total is
needed so we know when the employee has reached a limit of $3000 dollars in a
calendar year.

In this database we have data from this calendar year marking some employees
at the 3000 dollar limit. With the new year fast approaching I need this
running total to only calculate the semester dates that fall between 01.01.08
and 12.31.08.

Thank you for trying. I hope this is a better explanation of the problem.

 

[John W. Vinson]

Hmmm

Control Source for the running total text box =[Forms]![School
subform]![RunningTotal]

Control Source for the Semester Total text box named RunningTotal
=Sum([SemesterDollarAmount])

Ok let me see if I can explain this better.
Each employee that is entered into the database will have multiple recrods,
1 record for each class taken. Each class has a cost. The classes are listed
in a subform with the school information and the cost of the class. What I am
trying to do is calculate a running total on the main form. This total is
needed so we know when the employee has reached a limit of $3000 dollars in a
calendar year.

In this database we have data from this calendar year marking some employees
at the 3000 dollar limit. With the new year fast approaching I need this
running total to only calculate the semester dates that fall between 01.01.08
and 12.31.08.

Thank you for trying. I hope this is a better explanation of the problem.

The =Sum() expression will display the sum of whichever records are displayed
on the subform, regardless of date. Two solutions come to mind:

- Base the Subform on a Query selecting only dates within that range

or

- instead of the subform sum and the relay textbox, use a control source

=DSum("[SemesterDollarAmount]", "[nameofthetable]", "[EmployeeID] = " &
[txtEmployeeID] & " AND [Paymentdate] BETWEEN #1/1/08# AND #12/31/08#")

using your table name, employee ID field, and the name of a control on the
mainform containing the employee ID.

John W. Vinson [MVP]