supress record if subreport is null

If you use an inner join to the table used in the subreport that would be null, records will automatically be excluded.  That's what inner joins do.

Where is the subreport?

Cann you include th subreport tble in the main report?

mlmcc

The sub report is attached to the main report.  The sub report when nill does not show up.  No issue there.  The issue is that the main report always has data and always shows up.  I want the main report record to be excluded if there is no sub report data.

Thanks

I repeat my original answer.

Your subreport selects data based on a field in the main report, correct?  If so, then if you join the subreport table to your main report with an inner join to that field, then when there are no occurrences of that table, nothing will be selected.    And in large datasets, this design is likely to be vastly more efficient, because it would let the database server optimize the query.

The problem with this is that it would potentially increase the number of lines returned from the database where there is more than one row in the sub-report.

This messes with totals, and requires one to start playing with running totals etc to get them to work properly. Also you have to suppress the detail section and start using group footers to do the job.

However, I have generally found that 'detail' sub-reports can be avoided altogether, in these circumstances, with some creative formula and total use.

"Thanks_for_your_help", could you post a few more details about the data structure, and why you went for a sub-report in the first place?

The sub report is needed because the data is from a different data source than the main report.  The main report shows all instances of a particular occurrence.  The sub-report shows which are current instances of this occurrence.   Let's say this is report 1 and it works great and have no issues.

Report 2 needs to show only the occurrence that are current.  

Main report pulls data from data source 1
sub-report pulls data from data source 2

I was thinking last night is to try to reverse the report.  Make the report based on the data source 2 and do a sub-report on data source 1.

I let you know what I find.

And I repeat my answer a third time.  Just because they are two data sources doesn't change my answer.  Isn't there a common field you can join them with?

No there isn't.  The way the main report and the sub-report are joined is that there is 1 field in each data source that match.  The report looks for this match and connects to the main report and sub-report.

I tried that and couldn't get it to work. I have attached the file.
CRC-daily-manual-action-report.rpt

You need to put the GS_Action table in the main report.

I would like to thank all for your help.  After playing with it until 2am, I finally got it to work.  I did give bad information, I was incorrect in that there is not any fields that match between the 2 databases.  I forgot that in one database you had time/date field.  Database 2 only had a number in the time-date field that needed to be converted into a time/date before it could be compared to database 1.  That was the rub because there was not any fields that matched as is.  

I rewrote from scratch and somehow it actually work without a subreport and got only the data I needed.
NMAH-ACTIVE-MANUAL-ACTIONS-10222.rpt

Your answer got me thinking and I totally rewrote the report.

1) I was mistaken about sharing data.  In one database the data is classified as numbers and the other as a string.  Once I did the conversion I was able to work around this and get the report working.