Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing record data in a subreport (2000)

    I have a report with a subreport, with the link field as Country. I've revised the SQL statements for both the report and subreport to include a Criteria of [Updated] = 'Not updated'. The problem is that if there are no records to return for a particular Country in the main report, it doesn't show any records from the subreport.. even though there are valid records there - i.e. ones that HAVE [Updated] = 'Not updated'. Help! Andy.

  2. #2
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing record data in a subreport (2000)

    My original question might have been a little unclear. I've got two unrelated tables and I want to show data from both tables in a single report. They both have tables called Country and 'Updated'. So I built a report based on one of them, and then added a subreport to the Country footer (the Country being the natural link between them). I want to show all records where the 'Updated' field, in EITHER table, contains "Not updated". I don't know if this makes it any clearer?!

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing record data in a subreport (2000)

    If the tables are unrelated, a main report - subreport construction may not be ideal, but perhaps you can set the criteria in the record source of the main report to something like

    WHERE tblMain.Updated=False OR Exists (SELECT * FROM tblSub WHERE tblSub.Updated = False And tblSub.Country=tblMain.Country);

    If that doesn't work, consider basing the main report on a table or query listing all countries, and using subreports for both tables.

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing record data in a subreport (2000)

    The SQL statements almost works, but now it shows records on the main report that Don't have Updated = 'Not Updated', but I don't want to see these.. just the records for the subreport?!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing record data in a subreport (2000)

    In that case, I think that you will have to follow my other suggestion (using two subreports), for I don't see how you could show records in a subreport without a record in the main report. It is contradictory.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing record data in a subreport (2000)

    Hey we managed to get the first suggestion to work. Having revised the SQL statement, we added code to the On Format event for the Country to say if [Updated] = 'Not Updated' then set the visible property of the section to No.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing record data in a subreport (2000)

    I thought that you wanted to show records with [Updated] = 'Not Updated' ? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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