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
|
|||
|
|||
Programming subreport to only give certain amount of data
Hello
I have a report and subreport that is formatted and works the way I want it to. Now, I would like to automate the subreport to only give a certain amount of data. Currently I have a form, frmReport, that contains several unbound fields. These fields are linked to the query behind the report and the query behind the subreport. The report contains data concerning expenses. The report will go out to the various managers, and shows their employees expenses for the given month. The subreport shows the previous 12 month (month by month) at the manager level. At the present time, I have a BeginDate and EndDate field on frmReport, which gives me the data I want, but is not quite as automated as I would like. How can I program the report to show me the previous 12-months data on the subreport? For example, if I am looking at 'December_2009' data on the report, I would like to see 'December_2008' through 'November_2009' data in the subreport. Any help would be appreciated. Thanks in advance Devon |
#2
|
|||
|
|||
Programming subreport to only give certain amount of data
Hi Devon,
You can adjust the query for your subreport to use the DateAdd() function. For example, let's say you have the EndDate on your main report, using the expression: DateAdd("m", -12, EndDate) will return the date 12 months ago from your EndDate. Now, you can filter your subreport to something like: Between DateAdd("m", -12, EndDate) And DateAdd("m", -1, EndDate) Hope that helps... "Devon" wrote: Hello I have a report and subreport that is formatted and works the way I want it to. Now, I would like to automate the subreport to only give a certain amount of data. Currently I have a form, frmReport, that contains several unbound fields. These fields are linked to the query behind the report and the query behind the subreport. The report contains data concerning expenses. The report will go out to the various managers, and shows their employees expenses for the given month. The subreport shows the previous 12 month (month by month) at the manager level. At the present time, I have a BeginDate and EndDate field on frmReport, which gives me the data I want, but is not quite as automated as I would like. How can I program the report to show me the previous 12-months data on the subreport? For example, if I am looking at 'December_2009' data on the report, I would like to see 'December_2008' through 'November_2009' data in the subreport. Any help would be appreciated. Thanks in advance Devon |
#3
|
|||
|
|||
Programming subreport to only give certain amount of data
DBGuy
Thanks for the quick response. I know what you have included is correct, but I have not been able to get it to work in my database yet. The name of the column/attribute in the Main Report and in the SubReport is indeed EndDate. For some reason, when I include the DateAdd syntax you included in my subreport parameter query, it is not finding the EndDate column. I will continue to work on this and let you know my final solution. Thanks again for the information and the speedy response. Devon "theDBguy" wrote: Hi Devon, You can adjust the query for your subreport to use the DateAdd() function. For example, let's say you have the EndDate on your main report, using the expression: DateAdd("m", -12, EndDate) will return the date 12 months ago from your EndDate. Now, you can filter your subreport to something like: Between DateAdd("m", -12, EndDate) And DateAdd("m", -1, EndDate) Hope that helps... "Devon" wrote: Hello I have a report and subreport that is formatted and works the way I want it to. Now, I would like to automate the subreport to only give a certain amount of data. Currently I have a form, frmReport, that contains several unbound fields. These fields are linked to the query behind the report and the query behind the subreport. The report contains data concerning expenses. The report will go out to the various managers, and shows their employees expenses for the given month. The subreport shows the previous 12 month (month by month) at the manager level. At the present time, I have a BeginDate and EndDate field on frmReport, which gives me the data I want, but is not quite as automated as I would like. How can I program the report to show me the previous 12-months data on the subreport? For example, if I am looking at 'December_2009' data on the report, I would like to see 'December_2008' through 'November_2009' data in the subreport. Any help would be appreciated. Thanks in advance Devon |
#4
|
|||
|
|||
Programming subreport to only give certain amount of data
On Tue, 5 Jan 2010 08:16:01 -0800, Devon
wrote: For some reason, when I include the DateAdd syntax you included in my subreport parameter query, it is not finding the EndDate column. Hi Devon, If you're trying to use it in a parameter query, you'll need a full Forms reference for EndDate. That's because a query doesn't know where to find the value unless you tell it. The criteria will look something like: Between DateAdd("m", -12, Forms!frmReport!EndDate) And DateAdd("m", -1, Forms!frmReport!EndDate) Armen Stein Microsoft Access MVP www.JStreetTech.com |
#5
|
|||
|
|||
Programming subreport to only give certain amount of data
Thank you Armen and DBguy. This now works exactly the way I want.
I appreciate your time and information. Devon "Armen Stein" wrote: On Tue, 5 Jan 2010 08:16:01 -0800, Devon wrote: For some reason, when I include the DateAdd syntax you included in my subreport parameter query, it is not finding the EndDate column. Hi Devon, If you're trying to use it in a parameter query, you'll need a full Forms reference for EndDate. That's because a query doesn't know where to find the value unless you tell it. The criteria will look something like: Between DateAdd("m", -12, Forms!frmReport!EndDate) And DateAdd("m", -1, Forms!frmReport!EndDate) Armen Stein Microsoft Access MVP www.JStreetTech.com . |
#6
|
|||
|
|||
Programming subreport to only give certain amount of data
Glad we could help. Good luck with your project.
"Devon" wrote: Thank you Armen and DBguy. This now works exactly the way I want. I appreciate your time and information. Devon "Armen Stein" wrote: On Tue, 5 Jan 2010 08:16:01 -0800, Devon wrote: For some reason, when I include the DateAdd syntax you included in my subreport parameter query, it is not finding the EndDate column. Hi Devon, If you're trying to use it in a parameter query, you'll need a full Forms reference for EndDate. That's because a query doesn't know where to find the value unless you tell it. The criteria will look something like: Between DateAdd("m", -12, Forms!frmReport!EndDate) And DateAdd("m", -1, Forms!frmReport!EndDate) Armen Stein Microsoft Access MVP www.JStreetTech.com . |
#7
|
|||
|
|||
Programming subreport to only give certain amount of data
On Wed, 6 Jan 2010 07:31:01 -0800, Devon
wrote: Thank you Armen and DBguy. This now works exactly the way I want. I appreciate your time and information. No problem. Cheers! Armen Stein Microsoft Access MVP www.JStreetTech.com |
Thread Tools | |
Display Modes | |
|
|