How to skip empty pages in a report!!

[wattar]

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

[NeoPa]

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_For mat(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.

[wattar]

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

[NeoPa]

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 ;)

[MMcCarthy]

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:

 
SELECT ProjectID, Field1, Field2, etc
FROM ProjectTable LEFT JOIN MilestoneTable
ON ProjectTable.ProjectID=MilestoneTable.ProjectID
WHERE MilestoneID Is Not Null;
 

This will only return projects with milestone records.

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

 
SELECT ProjectID, Field1, Field2, etc
FROM ProjectTable INNER JOIN MilestoneTable
ON ProjectTable.ProjectID=MilestoneTable.ProjectID