Default value when Access report record source returns no data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a rather complex report that has 7 queries as a record source for the
report. The report displays a count of the records returned for each of the
seven queries. All works fine as long as each query returns at least one
record. My problem is (obviously) that sometimes one or more of the queries
returns no data. This causes ALL the display fields on the report to show
#Error. I have tried various methods to return a default value of 0 when a
query returns no data with no success. Does anyone have experiance with
setting a default value when a query returns no data? I have searched through
the voluminous help both in Access and online and have found no reference to
my problem. I'm sure I am missing something simple. Any help would be
appreciated.
 
It's not clear how a report can have 7 queries as its source. I'm guessing
you have 7 subreports, and you are trying to bring the total from each
subreport back to the main report?

If so, use the HasData property of the subreport. The Control Source of the
text box that picks up the value from the text box in the subreport will
look something like this:
=IIf([MySub].[Report].[HasData], Nz([MySub].[Report].[MyTextbox], 0), 0)
 
Thanks Allen, that worked

Allen Browne said:
It's not clear how a report can have 7 queries as its source. I'm guessing
you have 7 subreports, and you are trying to bring the total from each
subreport back to the main report?

If so, use the HasData property of the subreport. The Control Source of the
text box that picks up the value from the text box in the subreport will
look something like this:
=IIf([MySub].[Report].[HasData], Nz([MySub].[Report].[MyTextbox], 0), 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

rsmeds said:
I have a rather complex report that has 7 queries as a record source for
the
report. The report displays a count of the records returned for each of
the
seven queries. All works fine as long as each query returns at least one
record. My problem is (obviously) that sometimes one or more of the
queries
returns no data. This causes ALL the display fields on the report to show
#Error. I have tried various methods to return a default value of 0 when a
query returns no data with no success. Does anyone have experiance with
setting a default value when a query returns no data? I have searched
through
the voluminous help both in Access and online and have found no reference
to
my problem. I'm sure I am missing something simple. Any help would be
appreciated.
Click to expand...
Click to expand...
 
Back
Top