I currently have multiple users keying totals into 700+ spreadsheets to show daily, monthly and YTD figures. I want to house this info in Access 97 and I'm trying to set up a form that will display the calculated totals. How can I display the monthly totals, per plan, based on the current date listed on the form?

For instance, I have 700+ plans and I want to have the form show the current month total for plan A, based on the current date...Thanks!

I have been using Microsoft Access 97 since 1999. I think you could solve your problem by creating Reports from Access window.

First, make a Query consist of the Fields you want to appear in your Report. I think you should make a Crosstab Query.

Next Step is create a new Report using New Report Wizard. This new Report should based on the Query(Crosstab Query you've made). The Wizard then will ask you to group the new Report by any field you choose, for example: If your want to show the Total of a Month then you can group the new Report by field Month.

Hope this could help you solve the problem.

Happy Reporting!

Herru

First Create a field on your query design view using the DatePart() function like this

PlansThisMonth: DatePart("m",[DateField])

then in the search criteria paste the Date() function and the DatePart() function like this

DatePart("m",Date())

This will pull all plans for current month only

Let me know if this works

Sal

Remember that when using a crosstab query with a date, you must delimit your begin and start dates so that the data you want to show only spans one year. When Access formats your dates to just show the Month, Quarter, it will do so for all the data shown. So it will add records for October 2003, October 2002, October 2001, Etc. as one result, which will be wrong.

Sal