Access97- Report returning error as value 1

[Roal]

Hi,

I have compiled a report based on two sub reports which allows me to count the number of records in each sub report and return the following values:

1. Total No of records

Control source

=([rptDeadlines Met].[Report]![Total Day's Active Grand Total Sum]+[rptDeadline not met].[Report]![Days Active Grand Total Sum])

I.e. Report A contains 5 records, report B contains 5.
Total = 10

2. % ot total derived from report A.

Control source

=([rptDeadlines Met].[Report]![Total Day's Active Grand Total Sum]/[Text11])*100

In above example 50%.

How can I modify my selection criteria so that I get a value of 0%, if there are no records in report A or 100% if there are no records in Report B.

Thanks in advance,

Roal

 

[Trendsetter]

The method you have currently looks as though it will work to do what you want.... so I must assume there is a problem that you are not stating.

This could be that if there are no reports in A or B then the value in Text11 will not show or is Null, anfd the next step will fall over... if this is so you need to eliminate the possibility of Null occuring and put Nz() round the 2 references to the reports.
This will turn any Nulls into zeros and the steps will work.

 

[Roal]

Hi,

At the moment if I run the report and there are no records in either of the reports I get the message "#Error" in both fields.

I assume that this is because as you state, Text11 is returning a null value so the following calculation is failing.

I assume that you meen the following,

=(Nz([rptDeadlines Met].[Report]![Total Day's Active Grand Total Sum])+ Nz([rptDeadline not met].[Report]![Days Active Grand Total Sum]))

Or would I be better off using Nz() on the "Total Day's Active Grand Total Sum" and "Days Active Grand Total Sum" fields in the sub reports?

New to this whole Access business, so still working on the sintax...

Thanks again,

Roal

 

[Roal]

Hi Trendsetter,

At the moment if I run the report and there are no records in either of the sub reports I get the message "#Error" in both fields.

I assume that this is because as you state, Text11 is returning a null value so the following calculation is failing.

I assume that you meen the following,

=(Nz([rptDeadlines Met].[Report]![Total Day's Active Grand Total Sum])+ Nz([rptDeadline not met].[Report]![Days Active Grand Total Sum]))

Or would I be better off using Nz() on the "Total Day's Active Grand Total Sum" and "Days Active Grand Total Sum" fields in the sub reports?

New to this whole Access business, so still working on the sintax...

Thanks again,

Roal

 

[Trendsetter]

Why not try this and save some typing:
Dim ReportA As string
Dim ReportB As string
set ReportA = "[rptDeadlines Met].[Report]![Total Day's Active Grand Total Sum]"
set reportB = "[rptDeadline not met].[Report]![Days Active Grand Total Sum]"
Then we get:
1. Total No of records
Control source
=Nz(ReportA)+ Nz(ReportB)

Control source
=Nz((ReportA)/[Text11])*100

 

[Roal]

Hi Trendsetter,

Thanks for the help, it's very much appreciated,

I've tried running with this and I'm still geting the same error.
I think that this is because the underlying sub reports return an error value in their Sum total fields if they dont find any records matching the required criteria.

Could you/ or anybody else/ suggest a way to have them return a value of 0 when no records are found in the source query?

At the moment I have placed the sum total txtbox's in the Rpt footer with the following control source,

=(Count([Total Day's Active])

I thought maybe the IIf function would be the way to do it, but the help txt is limited and I'm not sure if it will work for this problem.

Thanks again,

Roal