disappearing subreports

[mollow]

Hi,

I have a strange problem. I have a report that produces invoice reports. It draws from two queries, the main one prompts the user to enter the "Job ID" and collates production costs. The second has no prompt and collates any recharges.

They both contain a field called "Job ID" and this is used as the child/master link between the report and subreport.

The problem is that occasionally, for no apparant reason, on running the invoice report the recharge subreport is not shown, even though the recharge query shows data should appear.

What is more bizarre is the fact that after playing around-yet not actually changing anything- it will randomly work again.

We are using Access '97 SR-2 on NT v4 machines. Has anyone else had similar problems? If so, is there a way to resolve the problem?

Thanks in advance,

Mike

 

[mollow]

Right i'm following up on myself here

After a lot of hair-tearing i've found what the problem is. The recharge subreport calculates a final total and uses:
=Sum(NZ([Total Cost]))

The problem is that occasionally when i try to run the subreport i will get a "NZ expression" error. Which I wouldn't mind, the only trouble is that the inconsistency...sometimes I will get the error message, sometimes i won't whilst running the same Job ID query.

So i'll change my question to has anyone encounted NZ problems?

Thanks,

Mike

 

[demoman]

Mike

I have been struggling with an inconsistant subreport for a few weeks now, and have yet to solve it. Like you, my report will work at times, and then not.

I have pretty much given up on believing Access can always correctly evaluate the child/parent link (at least in complex queries).

I am now trying to run the subreport WITHOUT the LINK, by just dynamically changing Filter property of the subreport. This is proving as difficult as the original problem. You cannot change the property if the subreport is closed. You cannot do it from the main report OPEN event, and you cannot do it from the subreport OPEN event, if it is called from a parent form. There is another concurrent string, from Val, going on this subject.

I will solve this, or work around it, but have not yet done so. If you solve it first, please DO make a post!

Thanks
Steve

 

[Lozere]

Not sure it solve your problem but in such situation, I use to base the subreport on a query where a condition operate on a field of the Report.
That is a Where clause in the SQL string of the query containing something like: (T_Active.Day)=[Reports]![L_Plan_Week]![ZT_PlaWk_Day])
and it did run for thousands of report issues.
Of course ZT_PlaWK_Day is in a section prior to the one containing the subreport.