Programming subreport to only give certain amount of data

[Devon]

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

 

[theDBguy]

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]

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

 

[Armen Stein]

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

 

[Devon]

Thank you Armen and DBguy. This now works exactly the way I want.

I appreciate your time and information.

Devon

 

[theDBguy]

Glad we could help. Good luck with your project.

 

[Armen Stein]

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