Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subreports with No Data (Access 2002)

    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!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subreports with No Data (Access 2002)

    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

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Subreports with No Data (Access 2002)

    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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    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...(!?)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Subreports with No Data (Access 2002)

    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
    Regards,

    Gary
    (It's been a while!)

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Subreports with No Data (Access 2002)

    Thanks Cecilia <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>. 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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subreports with No Data (Access 2002)

    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! <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

    <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

    Thank you SO much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •