How to Calculate Year to Date Totals?

[Jim]

I have a payroll table and check stub report that I need to show the year to
date totals for. Currently it adds all the records in the database instead
of just the current year in the report footer section. Is there an easy way
to add just the current year totals? I have access 2003 and the report is
based on form fields with no underlying query.

Thanks



Jim

 

[John W. Vinson]

I have a payroll table and check stub report that I need to show the year to
date totals for. Currently it adds all the records in the database instead
of just the current year in the report footer section. Is there an easy way
to add just the current year totals? I have access 2003 and the report is
based on form fields with no underlying query.

Well... base the report on a query with a criterion of

= DateSerial(Year(Date()), 1, 1)

Do you want to see the entire pay history on the report, but just the total
for this year? What's the recordsource of the sum textbox? How is the report
"based on form fields" ... and why?


John W. Vinson [MVP]

 

[Jim]

I want to see the total just for the current year. The record source for the
text box is some VB that adds up the totals for all the deductions and puts
them in the table for the week. The report is based on the underlying table
with all the fields, ie Soc Sec, Fed, State, etc. The report is actually
based on the fields in the table, but it uses the date field on the form to
filter just that week for the stub.

 

[John W. Vinson]

I want to see the total just for the current year. The record source for the
text box is some VB that adds up the totals for all the deductions and puts
them in the table for the week. The report is based on the underlying table
with all the fields, ie Soc Sec, Fed, State, etc. The report is actually
based on the fields in the table, but it uses the date field on the form to
filter just that week for the stub.

I'm sorry, I'm not visualizing this.

A textbox has a Control Source, not a recordsource. A Form has a recordsource.

A recordsource can be a Table or a Query. It cannot be VB (though it can be a
call to a Function written in VB).

If the report is based on the table, it could be based on a query on the table
instead; that query could filter by a week or by the year.

Could you post some more description? What is the filter? Do you want to
display just weekly data on the form, but year to date data in one textbox? If
so, a control source such as

=DSum("[someamount]", "sometable", "some criteria")

could be used (more specific answers available give more specific
information).

John W. Vinson [MVP]

 

[Jim]

Sorry to be so confusing. Here is a link to the form I'm using.
http://www.functionx.com/access/applications/payroll.htm I modified it to
only be weekly and the record source for the individual taxes are based on
the gross pay text box and functions I wrote in VB that put the totals in a
table (tblWages.) The form is bound to tblWages. The check stub report is
filtered by some VB code that matches the ID field (hidden) on the form in
the OnClick event.
Thanks for your help.

Jim

I want to see the total just for the current year. The record source for
the
text box is some VB that adds up the totals for all the deductions and
puts
them in the table for the week. The report is based on the underlying
table
with all the fields, ie Soc Sec, Fed, State, etc. The report is actually
based on the fields in the table, but it uses the date field on the form
to
filter just that week for the stub.

I'm sorry, I'm not visualizing this.

A textbox has a Control Source, not a recordsource. A Form has a
recordsource.

A recordsource can be a Table or a Query. It cannot be VB (though it can
be a
call to a Function written in VB).

If the report is based on the table, it could be based on a query on the
table
instead; that query could filter by a week or by the year.

Could you post some more description? What is the filter? Do you want to
display just weekly data on the form, but year to date data in one
textbox? If
so, a control source such as

=DSum("[someamount]", "sometable", "some criteria")

could be used (more specific answers available give more specific
information).

John W. Vinson [MVP]