Calculate Year to Date

[Jim]

I have a payroll check stub report that I need to show the year to
date totals for. The report is based on a table with no underlying query.The
report uses the date field on a form to filter just that week for the
stub.Thanks for any help.Jim

 

[Evi]

Do you mean you need to show the totals for the current year up to the
present date in a report that is filtered to show only one week?

Firstly, create a query with the data you need. Open the query in Design
view.

In an empty field in the Query type

Yr:Year([YourDateField])

(obviously, replace the text 'YourDateField' with the real name of your date
field)

Under this column, in the Criteria row of the query, type

Year(Date())

In the Criteria row under you date field type

<=Date()

Click the Totals button. Under your date field in the Total row choose Var
Under your Currency or Number field choose Sum

Close the query.
Open your report in design view

Drag this query into the report footer to create a subreport
Evi

 

[BruceM]

The criteria could be handled with a single expression for DateField:

Between DateSerial(Year(Date()),1,1) And Date()

There may need to be a way to handle a work week that spans two calendar
years. The Weekday function could be put to use, but the details would
depend on the business rules.

As an alternative to a query expression, a text box in the report footer
could contain the expression:
=Sum(SomeField)

Neither way is necessarily right or wrong. I'm just pointing out other
approaches.

Do you mean you need to show the totals for the current year up to the
present date in a report that is filtered to show only one week?

Firstly, create a query with the data you need. Open the query in Design
view.

In an empty field in the Query type

Yr:Year([YourDateField])

(obviously, replace the text 'YourDateField' with the real name of your
date
field)

Under this column, in the Criteria row of the query, type

Year(Date())

In the Criteria row under you date field type

<=Date()

Click the Totals button. Under your date field in the Total row choose Var
Under your Currency or Number field choose Sum

Close the query.
Open your report in design view

Drag this query into the report footer to create a subreport
Evi

I have a payroll check stub report that I need to show the year to
date totals for. The report is based on a table with no underlying query.The
report uses the date field on a form to filter just that week for the
stub.Thanks for any help.Jim

 

[Jim]

I will try both of these and see what happens.
Thanks

Jim

The criteria could be handled with a single expression for DateField:

Between DateSerial(Year(Date()),1,1) And Date()

There may need to be a way to handle a work week that spans two calendar
years. The Weekday function could be put to use, but the details would
depend on the business rules.

As an alternative to a query expression, a text box in the report footer
could contain the expression:
=Sum(SomeField)

Neither way is necessarily right or wrong. I'm just pointing out other
approaches.

Do you mean you need to show the totals for the current year up to the
present date in a report that is filtered to show only one week?

Firstly, create a query with the data you need. Open the query in Design
view.

In an empty field in the Query type

Yr:Year([YourDateField])

(obviously, replace the text 'YourDateField' with the real name of your
date
field)

Under this column, in the Criteria row of the query, type

Year(Date())

In the Criteria row under you date field type

<=Date()

Click the Totals button. Under your date field in the Total row choose
Var
Under your Currency or Number field choose Sum

Close the query.
Open your report in design view

Drag this query into the report footer to create a subreport
Evi

I have a payroll check stub report that I need to show the year to
date totals for. The report is based on a table with no underlying query.The
report uses the date field on a form to filter just that week for the
stub.Thanks for any help.Jim