Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Unanswered: Report Data Problems

    This may seem a silly one ebut its one Im having to deal with.

    A customer has a report populated from a crosstab query. However, there are occasions when one or more of the column headings will not be generated as there is no data for that heading.

    I have suggested that we redesign the report and query - they are not having that.

    The only way to calm the storm here is to try and set up the text boxes on the report so that :-

    If the field is not reported then print zero else print the data from the query..

    Is there a way to do this if so how? is there a way in the reports text box to do this under source code?

    Is there a way to do this via a module at the back of the text box?

    I know this seems silly but a result may be required in other reports in the same DB

    Many thanks for reading this

    Gareth

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    This site may be useful to you.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Thanks Nick

    I'm ok with a NO RECORDS TO DISPLAY process.

    To clarify.

    I report from an original crosstab query. Last year it (december) it was created to to account for data each month. The report was created which they still want to use.

    However, the report has existing "column" headings labled Jan Feb March Apr May etc etc

    Now - when we run the report looking for the months in 2006 and NOT 2005 (where there are 12 months of data) the report fails to operate because it can find months where there was no data for insstance Jan to Aug is ok - there is data but Sept has nothing and because of this the crosstab query cannot sent anything to the report. so the report fails to load. September Does not exist (yet). I cannot put dummy information in to the tables - to allow it to at least report zero (0). So, I'm thrying to find a way to make either the crosstab produce the zero or that the report sees that their is no record for that month to report and just places a zero in the text box - thus allowing the report to function without having to create a report for each month

    gareth

  4. #4
    Join Date
    Oct 2003
    Posts
    103

    Do your crosstab on another query rather than the table

    If you use another query such that (you have to get a little creative here depending on the data), and you have a one to many relationship defined, you can then do something like iif(isnull([date]),0,[date])

    Then you can do a crosstab query on the results of that query.

    For any further detail I'd have to look at your application.

  5. #5
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    I sumise that if I use this format it could go in to aprimary query, (one that gathers the data for a crosstab)? Then it should force the crosstab to create a zero where the not yet reached data is to occur

    ?

    Gareth

Posting Permissions

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