Replace #Error with Comment in Report

[WeeDram]

I've searched without success....

I have a report containing a sub-report which has a standard format, the main report has a report header saying "The following orders were shipped last week" and the detail section contains the order info. The sub-Report contains the header "The following orders are planned to ship this week" and the detail section again contains the order detail.

My problem arises if there are for example, no orders planned to ship this week, then the sub-report contains only #Error. I would like to replace this with "There are no orders planned to ship this week".

Is this possible?

 

[Remou]

You can use the HasData property of the subreport to trap this error.

 

[WeeDram]

Thanks REMOU,

but can you tell me how I handle things if it's not the sub-report that has no data?

Thanks

 

[tuzigoot]

WeeDram,

Have you had any luck fixing this issue? I have the same problem where my subreport has no data and I get the #error message. It is driving me nuts trying to get the error message to default to another value.

 

[Remou]

There is no difficulty in checking if a subreport has data or not, but it does not make sense to check if the main report does not have data, this should be handled in the query. To check if a subreport has data, you can use the HasData property of the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.This_Subreport.Report.HasData Then
    Me.txtNoData.Visible = False
Else
    Me.txtNoData.Visible = True
End If
End Sub

 

[dhookom]

If there is no data returned in your subreport, you shouldn't see anything where the subreport should appear. If you have a text box in the main report that depends on the subreport, then you can change your text box control source to something like:

=IIf(subrptCtrl.Report.HasData,subrptCtrl.Report.TxtTotal,Null)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tuzigoot]

Thanks Dhookom for all your suggestions up to this point.

My specific problem is that I run a set of unique reports via a macro process (no subreports), then I have a summary memo report that derives values from text boxes contained in these unique reports. If the unique reports contain text box values >0, then my summary memo populates with the correct values.

If, however, the other unique reports have text box values =0 or are null, then my summary memo returns the #error message.

I have tried to incorporate the .hasdata into an iif statement with no luck--I get a #Name? error. Forgive my naivitae in this matter, but I am new to this.

Again, I appreciate your help!

 

[dhookom]

I don't understand when your first stated "same problem where my subreport has no data" and then later "no subreports".

Can you provide specifics on where your values are coming from and where you eventually want them.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tuzigoot]

DHookom,

I actually don't have sub reports. I have 4 unique reports that summarize certain data. Within those reports I have a seperate text box that totals the # records in that report (Total Records).

Once these 4 reports run, I have another summary report that looks at the "Total Records" text box values from the prior 4 reports and tries to reflect these in a memo style report.

If the Total Records values in the other 4 reports are >0, then my summary reporting memo will pick up those values. If the Total records values in the other reports are null or 0, then my summary memo will return the #error message.

I can use an Iff statment in the Total Records count text boxes (in the 4 unique reports) to derive "0" or null value where it does not see any records. I can't the 0 or null values to be recognized by the final summary report.

 

[dhookom]

You should not create a report that depends on values generated in other reports that aren't nested as sub/main reports. Find a method to get the values that doesn't depend on running your reports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]