 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2008Calculate 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 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 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? [quoted text clipped - 32 lines] >> report uses the date field on a form to filter just that week for the >> stub.Thanks for any help.Jim I will try both of these and see what happens. Thanks Jim > The criteria could be handled with a single expression for DateField: > [quoted text clipped - 48 lines] >>> report uses the date field on a form to filter just that week for the >>> stub.Thanks for any help.Jim |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.