Failing totals when there are sub reports with no data.

[Guest]

Hello and Thanks for the Help in Advance.

I am working with Sub Reports in Access 2002.

I have VBA code in the Main Report which gives a %. It is
calling a field on a sub report. The problem is sometimes
there is no data on this sub report so I get 2 errors
Error "Run-time error '2427':
You entered an expression that has no value."
Then I get "Compile error:
Variable not defined"


Here is one of the lines it is failing on.

PercentComm = ***(Nz(Sub_JobsProducedComm!TPrice, 0)*** +
Nz(Sub_NoPOrderedComm!TPrice, 0)) / Nz(Sub_QTOCComm!
TPrice, 0)

It fails in between the ***

Any Help would be appreciated.

Thanks, Leo Snetsinger

 

[david epsom dot com dot au]

Re: Failing totals when there are sub reports with no data.

Actually, when there is no data, there is no sub-report.
The whole sub-report goes missing when there is no data.

So this is not null:
Sub_NoPOrderedComm!TPrice

There is no 'Sub_NoPOrderedComm' to find, and no TPrice
to look at.

One solution is to write VBA code to calculate the values
and write the values to labels on the report. In VBA you
can use
On Error Resume Next

and ignore the error caused by refering to a non-existant
control on a non-existant subreport.

(david)

 

[Marshall Barton]

Hello and Thanks for the Help in Advance.

I am working with Sub Reports in Access 2002.

I have VBA code in the Main Report which gives a %. It is
calling a field on a sub report. The problem is sometimes
there is no data on this sub report so I get 2 errors
Error "Run-time error '2427':
You entered an expression that has no value."
Then I get "Compile error:
Variable not defined"


Here is one of the lines it is failing on.

PercentComm = ***(Nz(Sub_JobsProducedComm!TPrice, 0)*** +
Nz(Sub_NoPOrderedComm!TPrice, 0)) / Nz(Sub_QTOCComm!
TPrice, 0)

It fails in between the ***

You need to check if a subreport has any data before trying
to use the possibly nonexistent data.

If Me.Sub_JobsProducedComm.Report.HasData Then
PercentComm = Me.Sub_JobsProducedComm.Report.TPrice
Else
PercentComm = 0
End If

 

[Leo]

Thanks Very Much.....


I have used the HasData in three reports already.. Great
Help...

Leo