Solved Detect if query is null (no results) to prevent a subreport from loading

[mafhobb]

Hi.
I have a report with a number of subreports. These subreports get their data from a query.
How can I detect if one those queries returns no results so I can hide the subreport on the report?
mafhobb

 

[Minty]

Reports have an On No Data property - you can use that.
An alternative is to take the sub report control and set it to 0.001 tall and can grow - it will only appear if there is content.

 

[bastanu]

Or use srptYourReport.Visible=(dCount("*","[qrySubReportRecordSource]")>0)
Cheers,

 

[Isaac]

Both methods are good

 

[mafhobb]

What if I have an extended total on the main report that adds the subtotals on the subreports? How can I tell the report's extended total textbox to NOT add that specific subreport's subtotal, which has a value of null?
mafhobb

 

[bastanu]

Use the Nz function: ExtendedTotal:Nz(txtsrptOneTotal,0)+ Nz(txtsrptTwoTotal,0)+...

 

[mafhobb]

=Val(Nz([Report]![rptMaintenanceDoneSubrpt]![txtMaintenanceSubtotal].[Value],0))+Val(Nz([Report]![rptMaterialsDoneSubrpt]![txtMaterialsSubtotal].[Value],0))+Val(Nz([Report]![rptReservationsDoneSubrpt]![txtReservationsSubtotal].[Value],0))

The above is what I have in the extended total textbox in my report, but something strange happens....
If txtReservationsSubtotal has a value of "0", the extended total comes out with a "Type" error, However, if I simply click on that subtotal textbox, then the extended total on the report updates correctly and that "Type" error is replaced by a numeric value.
It is almost as if it did not recognize the value in that subtotal textbox until I clicked on it.
Why?
Does this have something to do with how the report refreshes and/or gets the data from the subreports?
mafhobb

 

[bastanu]

I assume you tried without Val() and .Value and got the same. You say you click on the subreport so you must open it in Report view which I never use as I can't really see the point. What happens in Print Preview (or Print)? If you still have the issue try to move the expression to the Format event of the report section that has the extended total. If that also fails replace the reference to the subreport textboxes with dSums based on each subreport record source.

Cheers,

 

[mafhobb]

I did try without VAL, but I will try again, just in case that I had a typo somewhere.

If I open the report in PrintPreview or Print it, if any subreport has a value of "0", the subreport shows the "0", but the extended sum on the main report still shows "Type". In this case, however, clicking on the subreport does not fix the issue with the extended sum.

"On Format" event? I will try that.

Replace the reference to the subreport? So you mean to redo all the sums on the main report?

mafhobb

 

[bastanu]

Try to replace the extended total expression with this (adjust for the field and query names):

=dSum("[AmountToSum]","[qrySource_for_rptMaintenanceDoneSubrpt]")+dSum("[AmountToSum]","[qrySource_for_rptMaterialsDoneSubrpt]") + dSum("[AmountToSum]","[qrySource_for_rptReservationsDoneSubrpt]")

Cheers,

 

[mafhobb]

Try to replace the extended total expression with this (adjust for the field and query names):

=dSum("[AmountToSum]","[qrySource_for_rptMaintenanceDoneSubrpt]")+dSum("[AmountToSum]","[qrySource_for_rptMaterialsDoneSubrpt]") + dSum("[AmountToSum]","[qrySource_for_rptReservationsDoneSubrpt]")

Cheers,

I have replaced the source to this: =dSum("[Subtotal]","[qryMaintenanceInvoice]") and it worked fine. (This query does return results)

Then I added a second query which also returns results and it worked: =dSum("[Subtotal]","[qryMaintenanceInvoice]")+dSum("[TotalItemCost]","[qryMaterialsInvoice]")

Then I added a third query although this third query does not return results and the textbox is now blank: =dSum("[Subtotal]","[qryMaintenanceInvoice]")+dSum("[TotalItemCost]","[qryMaterialsInvoice]") + dSum("[Comission]","[qryReservationsInvoice]")

Then I added data so so qryReservations would return results and this worked.

Then I deleted data so qryMaterialsInvoice would not return any results and it failed again.

Then I added the Nz function and IT WORKED!!!! Yeeeeeeeeeeeeeessssssssss!!!

Many, many thanks!!!

Now, I see what we are doing here, bypassing the report data for the calculation and going directly to the query. In an effort to learn and not run into the same issue again, do you know why the first method would not work? Is it always best to go directly to the query in the case of reports? Is it an "order" thing, meaning that maybe the report is trying to add values that do not exist as maybe the subreport loads after the main report tries to make the calculation?

Many thanks again

mafhobb

 

[bastanu]

Glad to hear you solved it!. In reports calculated controls should be based on the field names (of the report's recordsource) not control names. Usually the subreports load before the main report (at least the subforms do) so that is probably not the case (and putting the original formula in the Format event of the section would have eliminated that as a cause).

Total of sub-reports within a report

I am trying to build a summary total in the report footer of my main report which includes within its detailed section 7 other sub-reports. I created a series of running sum textboxes and tried

answers.microsoft.com

Can't Sum On the Report Footer

Hello Friends, Need your help! Something wrong, Can't Sum On the Report Footer In the detail I have Unbound Text-box "LossMoney" With Expression =Nz([Price])*Nz([Qt3]) working ok giving the correct results. Now at the footer of the report I want to sum the "LossMoney" Tried many variants, but...

www.access-programmers.co.uk

Cheers,
Vlad

 

[The_Doc_Man]

Now, I see what we are doing here, bypassing the report data for the calculation and going directly to the query.

I want to emphasize that your realization is incredibly important. You are, indeed, going the right way. Particularly if you know something won't be there (potentially), it is of questionable design to have to depend on that thing that is really just a "middle-man" in your process. The real data was always there behind the scenes. It is USUALLY the best idea to gather data directly than indirectly where possible. Getting values from a report means indirect gathering. Getting underlying data from your query is direct. Also, PROBABLY faster. (Not always faster, but it is a very good bet.)

I am posting to act as a sort of "cheerleader" from the sidelines in this case, because other issues diverted me from getting involved before you and the forum came to a resolution. SO... good luck on your project and congratulations on finding something workable.