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

mafhobb

Registered User.
Local time
Today, 08:59
Joined
Feb 28, 2006
Messages
1,170
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

AWF VIP
Local time
Today, 14:59
Joined
Jul 26, 2013
Messages
8,760
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

AWF VIP
Local time
Today, 06:59
Joined
Apr 13, 2010
Messages
858
Or use srptYourReport.Visible=(dCount("*","[qrySubReportRecordSource]")>0)
Cheers,
 

mafhobb

Registered User.
Local time
Today, 08:59
Joined
Feb 28, 2006
Messages
1,170
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

AWF VIP
Local time
Today, 06:59
Joined
Apr 13, 2010
Messages
858
Use the Nz function: ExtendedTotal:Nz(txtsrptOneTotal,0)+ Nz(txtsrptTwoTotal,0)+...
 

mafhobb

Registered User.
Local time
Today, 08:59
Joined
Feb 28, 2006
Messages
1,170
Code:
=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
 
Last edited:

bastanu

AWF VIP
Local time
Today, 06:59
Joined
Apr 13, 2010
Messages
858
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

Registered User.
Local time
Today, 08:59
Joined
Feb 28, 2006
Messages
1,170
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

AWF VIP
Local time
Today, 06:59
Joined
Apr 13, 2010
Messages
858
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

Registered User.
Local time
Today, 08:59
Joined
Feb 28, 2006
Messages
1,170
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

AWF VIP
Local time
Today, 06:59
Joined
Apr 13, 2010
Messages
858
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).
Cheers,
Vlad
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:59
Joined
Feb 28, 2001
Messages
20,625
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.
 

Users who are viewing this thread

Top Bottom