Calculating Year-To-Date

  • Thread starter Thread starter Becca
  • Start date Start date
B

Becca

I have a report that is attached to a query. I need to
calculate MTD and YTD totals.

The month to date is easy. I have created a new text box
and made the control source the textbox field name and
running sum is overall. That works find because the query
prompts you for the date ranges (04/01/04 to 4/30/04). I
get my MTD totals.

But they want YTD totals in addition. Do I make a new
report and have them query the entire year, but then all
365 days worth of pages will be previewed and I would just
want them to see the last page which would have the YTD.
Any advice? TIA!
Rebecca
 
You should be able to use the DSum function in a textbox to get the Year to
Date totals.

Example Control Source:
=DSum("[AmountField]", "
", "Year([DateField])=" & Year(Date()))

If you have items entered for future dates, you'll need a second criteria
limiting the date field to <=Date().
 
You can also create a new totals query that can be included in your report's
recordsource. If the totals query contains only one record, you don't need
to join it to any other table. If there are multiple records (grouped by a
field) then you will need to join the fields.

--
Duane Hookom
MS Access MVP


Wayne Morgan said:
You should be able to use the DSum function in a textbox to get the Year to
Date totals.

Example Control Source:
=DSum("[AmountField]", "
", "Year([DateField])=" & Year(Date()))

If you have items entered for future dates, you'll need a second criteria
limiting the date field to <=Date().

--
Wayne Morgan
MS Access MVP


Becca said:
I have a report that is attached to a query. I need to
calculate MTD and YTD totals.

The month to date is easy. I have created a new text box
and made the control source the textbox field name and
running sum is overall. That works find because the query
prompts you for the date ranges (04/01/04 to 4/30/04). I
get my MTD totals.

But they want YTD totals in addition. Do I make a new
report and have them query the entire year, but then all
365 days worth of pages will be previewed and I would just
want them to see the last page which would have the YTD.
Any advice? TIA!
Rebecca
Click to expand...
Click to expand...
 
Back
Top