Subreports with No Data (Access 2002)

Topic

New Reply

Hi All!

I have a gigantic report with tons of subreports in it, because the data is all coming from different queries. It’s a really big summary report, where the queries are generally queries of queries. There is a user interface that allows the user to select which region to display on the summary report. When you select All regions, I have no isses with the report. But when a single region is displayed, it’s possible for a region to not have any of a particular type of sale, and therefore no data to report for that sale.

The problem I am having is when one of the subreports has no data. If the subreport has no data, then not even a placeholder shows for that subreport.

There are two separate problems. The first is a regular select query, where it might show type of sale, number of sales, then dollar amount of sales. The second type generally ends up being a crosstab query, where the fields of National and Regional can be hardcoded in the query properties, but if there is no data, then nothing displays (in other words, I can hardcode the columns but not the rows).

Has anyone been able to create a query that always has a set of standard rows, possibly displaying 0’s when there are no values? If I could do this, then my subreports would show the basic information. Any ideas on where to look to do this?

TIA!

Reply | Quote

Replies

What you could do is to do a mismatched query to get those entries for you then test for null and replace with zero.
You may have to use a temporary table to get this to work.

Just an idea really.

Pat

Reply | Quote

Cecilia,
I don’t completely understand much of it but MAYBE I can help you with the last question.
First I want to generalise your question in order to know whether I got it right.
Does
“Has anyone been able to create a query that always has a set of standard rows, possibly displaying 0’s when there are no values? If I could do this, then my subreports would show the basic information”
mean something like:
“I have a crosstab query showing sales type (row header), region (column header) and sum of profits (value). What do I need to let all sales types (= rows) appear, instead of only those shown in the crosstab?”
Am I right?
Then, a first step might be taken by a next query combining (all fields from) your crosstab query on the left with a recordset representing all possible sales types (using a sales type lookup table or query) on the right. Now: right outer join them by their common field (sales type / sales type ID?). As such, the result should show a row for each possible sales type.
I don’t know how you should insert the ‘0’, but that must be possible too. In the worst case, by using a calculated field for each column header in your crosstab (e.g. “Region1_: Iif(IsNull(Region1);0;Region1)”.
I hope I don’t confuse you more that it helps
Hasse
ps I remember Hellen Feddema writing (more than?) once about handling empty (sub?)reports with code. So for that aspect of your question, searching the Access Archons might reveal something adjustable for your needs…(!?)

Reply | Quote

Hmmm. So basically what you’re saying is build a lookup table and then put it in a query with an outer join.

IT WORKS! The 0 thing will probably slow my query down a bit too much, but I suspect that it will work too!

Thank you SO much!

Reply | Quote

Thanks Cecilia . I’m just a bit embarassed for (instead of my Iif(…)-proposal) not having used the probably much faster Nz(), mentioned by our moderator(s). But that’s why it’s always so good to have them around

Reply | Quote

Cecilia,

I encounter this frequently and remedy it by using the Nz (test data, 0) in the queries that generate the query for the subform or by writing dummy data such as zeroes in the table storing the data if no data is available so the subreports do not open blank. On data entry, Hans had a tip to open a form in data entry mode but that doesn’t apply here.

Good Luck

Reply | Quote