Reports, Sub-reports running sum and no data

[hlogoma]

Hi,

I have a rptServiceBalanceSheet, and it has a number of Sub-reports.

In many instances some of the Sub-reports will have no data and as a result the total for the Sub-report in the Sub-report footer will give me a #Error.

This Sub-reports, rptsubMiscCodes, has the following controls:

ContractId CodeName Total .... there is another total below this one which is used for the running sum over the group and it is hidden.

The control source is ContractId and then CodeName and finally for both of the Totals we have an argument:

=IIf(DCount("[CodeName]","qryMiscCodes","[CodeName] = '" & [ctlCodeName] & "' And [ContractID] = '" & [ctlContractID] & "' And [EmployeeName] = '" & [ctlEmployeeName] & "' ")>1,DSum("Nz([DirectTime])+Nz([IndirectTime])","qryMiscCodes","[CodeName] = '" & [ctlCodeName] & "' And [ContractID] = '" & [ctlContractID] & "' And [EmployeeName] = '" & [ctlEmployeeName] & "' "),DSum("Nz([Total])","qryMiscCodes","[CodeName] = '" & [ctlCodeName] & "' And [ContractID] = '" & [ctlContractID] & "' And [EmployeeName] = '" & [ctlEmployeeName] & "' "))

In the format attribute of the property sheet for the Total control including the one for the running sum, I have 0.00;0.00;0.00;0.00. I also use the same format in the queries for these Sub-reports.

The Sub-report works well and gives accurate and approriate totals if the Sub-report has data. Even in the detail section the Totals show a zero if there is no data, but as noted earlier the total in the Sub-report footer, which references the name of the running total in the detail section,ie. =Nz([MiscServicesRunningSum]), gives me a #Error.

Any ideas as to how get something other than a #Error in the Total for the Sub-report footer?

Thanks for any help, suggestions or pointers.

Tom.

 

[hlogoma]

I found a solution for the sub-reports totals, it was a solution posted in the Microsoft Access Reports forum where the suggestion was to use ctl in the report footer, in my case it is named, CSPServicesRunningSum, and then to use this expression:

=IIf([Report].[HasData],[Report].[CSPServicesRunningSum],0)

And voila, I had 0 in my subreport totals. This was important for me since the Main reports sums all of these sub-reports.

Unfortunately in print preview I get an #Error for the main report total whereas otherwise it works just great.

Any suggestions on how I can get the main report total to give me the same total that I get in report view.

Thanks for any suggestion and also thanks for the solution.

Tom.

 

[hlogoma]

Hi,

I found a solution to the #Error in the Main Report Total at this site. The solution that I used is at the end of the article:
http://office.microsoft.com/en-us/access-h...A010209281.aspx

I just thought I should post all the solutions that I have come across, so that if anyone follows this thread and they are wondering how to solve this same issue at least they can explore the solutions.

Onward.

Tom.