A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Programming subreport to only give certain amount of data



 
 
Thread Tools Display Modes
  #1  
Old January 4th, 2010, 11:04 PM posted to microsoft.public.access.reports
Devon
external usenet poster
 
Posts: 30
Default 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  
Old January 4th, 2010, 11:18 PM posted to microsoft.public.access.reports
theDBguy[_2_]
external usenet poster
 
Posts: 29
Default 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  
Old January 5th, 2010, 05:16 PM posted to microsoft.public.access.reports
Devon
external usenet poster
 
Posts: 30
Default 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  
Old January 6th, 2010, 02:05 AM posted to microsoft.public.access.reports
Armen Stein[_2_]
external usenet poster
 
Posts: 157
Default 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  
Old January 6th, 2010, 04:31 PM posted to microsoft.public.access.reports
Devon
external usenet poster
 
Posts: 30
Default 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  
Old January 7th, 2010, 05:34 AM posted to microsoft.public.access.reports
theDBguy[_2_]
external usenet poster
 
Posts: 29
Default 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  
Old January 7th, 2010, 06:55 PM posted to microsoft.public.access.reports
Armen Stein[_2_]
external usenet poster
 
Posts: 157
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.