If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculate Year to Date
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 |
#2
|
|||
|
|||
Calculate Year to Date
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 "Jim" wrote in message ... 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 |
#3
|
|||
|
|||
Calculate Year to Date
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. "Evi" wrote in message ... 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 "Jim" wrote in message ... 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 |
#4
|
|||
|
|||
Calculate Year to Date
I will try both of these and see what happens.
Thanks Jim "BruceM" wrote in message ... 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. "Evi" wrote in message ... 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 "Jim" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|