<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2726.2500" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV>
<DIV><STRONG>Nancy,</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV> If the RecordSource for a subreport has no data, the
only event that gets fired (for the report acting as SourceObject for the
subreport control) is its open event. The subreport behaves as if
its various sections (and controls located thereon) do not exist. Calculations
referring to such controls are therefore bound to return an error.</DIV>
<DIV> </DIV>
<DIV> In order to overcome such a situation, the
formula to be used in the parent report (either in a calculated text
box or in VBA code) has to be as follows (Q_SR is the name of query
acting as RecorSource for subreport, SR is the name of subreport control on
parent report, and TxtSR is the name of control on subreport
whose value is to be obtained) -</DIV>
<DIV> </DIV>
<DIV> ReqdValue = IIf(DCount("*","Q_SR") > 0, SR("TxtSR"),
0)</DIV>
<DIV> </DIV>
<DIV> It would be nice if you could kindly try it out and let
me know.</DIV>
<DIV> </DIV>
<DIV>Regards,</DIV>
<DIV><STRONG>A.D.Tejpal</STRONG></DIV>
<DIV><STRONG>--------------</STRONG></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=LYTLEN@peoplepc.com href="mailto:LYTLEN@peoplepc.com">Nancy Lytle</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">Access Developers discussion and
problem solving</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, October 09, 2003
06:31</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD] What to do when
subreport used as textbox source hasno data</DIV>
<DIV><BR></DIV>Thanks everyone, I used the IIf statement and it works for the
Sections that<BR>have no subreport data, but for the sections with it, it nows
comes up<BR>"error#".<BR><BR>Code I used for the txtAB Control
Source<BR><BR>=[njl_qryProgInitAuth].[Report]![Initial<BR>Authorization]+[njl_qryProgChanges].[Report]![SumOfAuthorizationChanges]-IIf<BR>([rptExec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumComm],0)-IIf([rpt<BR>Exec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumOblig],0)<BR><BR>I
guess I should have said that this subreport is in the ProgramCode
Footer<BR>section. Could that be a problem?<BR><BR>TIA,<BR>Nancy<BR>-----
Original Message ----- <BR>From: "Ron Allen" <<A
href="mailto:chizotz@charter.net">chizotz@charter.net</A>><BR>To: "Access
Developers discussion and problem solving"<BR><<A
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>><BR>Sent:
Wednesday, October 08, 2003 7:39 PM<BR>Subject: Re: [AccessD] What to do when
subreport used as textbox source has<BR>no data<BR><BR><BR>> Hi
Nancy,<BR>><BR>> The problem, I believe, is that when there is a null
value<BR>> the whole expression evaluates to null. I have solved
this<BR>> a few ways, but the easiest in most cases is something<BR>>
like this: txtAB = iif(Sub1.field,Sub1.field,0) +<BR>>
iif(sub2.field,sub2.field,0)- iif(sub3.field,<BR>> sub3.field,0). I work
mainly in Acc97, but in Acc2k and<BR>> above there is also a NV function
you could look at. This<BR>> isn't perfect for all cases, but it does work
for me.<BR>><BR>> HTH,<BR>><BR>>
Ron<BR>><BR>><BR>><BR>> On Wed, 8 Oct 2003 19:00:43
-0400<BR>> "Nancy Lytle" <<A
href="mailto:LYTLEN@peoplepc.com">LYTLEN@peoplepc.com</A>> wrote:<BR>>
>I have a main report with 3 subreports, all are related<BR>> >by the
field<BR>> >ProgramCode. There is a record in three of the
reports<BR>> >(Main and 2<BR>> >subreports) for every
ProgramCode. I also use the values<BR>> >in the subreports<BR>>
>to populate a text box - txtAB = Sub1.field + sub2.field-<BR>>
>sub3.field -<BR>> >sub3.field. This works fine except for the
case where<BR>> >the ProgramCode has<BR>> >no record in
Subreport3, then that subreport doesn't show<BR>> >and the textbox
is<BR>> >filled with error#. How do I get the textbox to
use<BR>> >txtAB = Sub1.field +<BR>> >sub2.field where there is no
sub report for the linked<BR>> >field.<BR>> ><BR>> >Hope I
explained this okay.<BR>> >TIA<BR>> >Nancy L<BR>> ><BR>>
>N_Lytle@terpalum.umd.edu<BR>>
>240-274-0069<BR></BLOCKQUOTE></DIV></BODY></HTML>