By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,824 Members | 2,410 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,824 IT Pros & Developers. It's quick & easy.

How to skip empty pages in a report!!

P: 2
Hi all,
I will briefly tell you about the structure of my databse, and then will ask you my question.
I have a database for project tracking, with a table for projects, and some other tables related to the projects table (i.e. Milestones)

I have a report based on a query that selects all the projects that I have and the report is grouped by the project ID. Within this report, I have a subreport for Milestones. the query for the Milestones subreport selects all the project milestones where the project ID = the "Report" ProjectID. This makes the Milestones subreport pick only the milestones for each project ID as per the grouping done in my parent report.

The Problem:
Some projects have no milestones!
I end up having a page in the report where on the top of the page, there is the project name and nothing below it. So of course this doesn't look very presentable.

The Question:
Is there a way using VB or access to completely skip such pages from the repot so I would end up with a report for all projects that actually have milestones?

Thanks for your help!

Wael
Nov 23 '06 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,489
Try setting some code in the OnFormat event of your page header.
You'll have to play around (experiment) to find out exactly what you need to do.
Format Event Event Procedures


To create an event procedure that runs when the Format event occurs, set the OnFormat property to [Event Procedure], and click the Build button .

Syntax

Private Sub sectionname_Format(Cancel As Integer, FormatCount As Integer)

The Format event procedure has the following arguments.

Argument Description
sectionname The name of the report section whose Format event procedure you want to run.
Cancel The setting determines if the formatting of the section occurs. Setting the Cancel argument to True (1) cancels formatting of the section.
FormatCount An Integer value that specifies whether the Format event has occurred more than once for a section. For example, if a section doesn't fit on one page and part of it moves to the next page of the report, Microsoft Access sets the FormatCount argument to 2.


Remarks

If you cancel formatting, Microsoft Access doesn't format the section for printing and prints the next section instead. You can use this event procedure to skip a section in a report without leaving a blank space on the page when the report is printed.
Nov 23 '06 #2

P: 2
Hi Neo,
Thanks for your reply.
I already tried that for almost 2 whole days, but I didn't reach anything.

However, I found a workaround (an ugly one!!), which is tooooo slow, but at least, it is working.

Before opening the report, I had to simulate the running of the parent report query along with the subreport query, and get a list of the project ID's that have Milestones. And then, I created a query at runtime using the QueryDefs, and based the final report on this created query.

It works, but as I told you, it is too slow.

I would still appreciate if someone can help me with a better solution.

Thanks a lot
Nov 23 '06 #3

NeoPa
Expert Mod 15k+
P: 31,489
OK Wattar.
Post the SQL you're running atm & we'll see what we can come up with.
I suspect it's a question of using a LEFT JOIN rather than an INNER one - but we'd need to see exactly what you've got before moving on that.

BTW Neo is my son's tag - hence NeoPa ;)
Nov 23 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi all,
I will briefly tell you about the structure of my databse, and then will ask you my question.
I have a database for project tracking, with a table for projects, and some other tables related to the projects table (i.e. Milestones)

I have a report based on a query that selects all the projects that I have and the report is grouped by the project ID. Within this report, I have a subreport for Milestones. the query for the Milestones subreport selects all the project milestones where the project ID = the "Report" ProjectID. This makes the Milestones subreport pick only the milestones for each project ID as per the grouping done in my parent report.

The Problem:
Some projects have no milestones!
I end up having a page in the report where on the top of the page, there is the project name and nothing below it. So of course this doesn't look very presentable.

The Question:
Is there a way using VB or access to completely skip such pages from the repot so I would end up with a report for all projects that actually have milestones?

Thanks for your help!

Wael
If I understand this correctly there is no milestone data currently present in the main report records. If this report is currently based on the table change it to a query or if it may already be a query.

Either way you need to add the milestones table to the query not as a returned field but as criteria only. And exclude null values.

Something like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT ProjectID, Field1, Field2, etc
  3. FROM ProjectTable LEFT JOIN MilestoneTable
  4. ON ProjectTable.ProjectID=MilestoneTable.ProjectID
  5. WHERE MilestoneID Is Not Null;
  6.  
This will only return projects with milestone records.

Can also be written like this (as per NeoPa's suggestion)

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT ProjectID, Field1, Field2, etc
  3. FROM ProjectTable INNER JOIN MilestoneTable
  4. ON ProjectTable.ProjectID=MilestoneTable.ProjectID
  5.  
Nov 23 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.