[AccessD] What to do when subreport used as textbox source has no data

Thanks everyone, I used the IIf statement and it works for the Sections that
have no subreport data, but for the sections with it, it nows comes up
"error#".

Code I used for the txtAB Control Source

=[njl_qryProgInitAuth].[Report]![Initial
Authorization]+[njl_qryProgChanges].[Report]![SumOfAuthorizationChanges]-IIf
([rptExec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumComm],0)-IIf([rpt
Exec_SP].[Report].[HasData],[rptExec_Sp].[Report]![SumOblig],0)

I guess I should have said that this subreport is in the ProgramCode Footer
section. Could that be a problem?

TIA,
Nancy
----- Original Message ----- 
From: "Ron Allen" <chizotz at charter.net>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, October 08, 2003 7:39 PM
Subject: Re: [AccessD] What to do when subreport used as textbox source has
no data


> Hi Nancy,
>
> The problem, I believe, is that when there is a null value
> the whole expression evaluates to null. I have solved this
> a few ways, but the easiest in most cases is something
> like this: txtAB = iif(Sub1.field,Sub1.field,0) +
> iif(sub2.field,sub2.field,0)- iif(sub3.field,
> sub3.field,0). I work mainly in Acc97, but in Acc2k and
> above there is also a NV function you could look at. This
> isn't perfect for all cases, but it does work for me.
>
> HTH,
>
> Ron
>
>
>
> On Wed, 8 Oct 2003 19:00:43 -0400
>   "Nancy Lytle" <LYTLEN at peoplepc.com> wrote:
> >I have a main report with 3 subreports, all are related
> >by the field
> >ProgramCode.  There is a record in three of the reports
> >(Main and 2
> >subreports) for every ProgramCode.  I also use the values
> >in the subreports
> >to populate a text box - txtAB = Sub1.field + sub2.field-
> >sub3.field -
> >sub3.field.  This works fine except for the case where
> >the ProgramCode has
> >no record in Subreport3, then that subreport doesn't show
> >and the textbox is
> >filled with error#.  How do I get the textbox to use
> >txtAB = Sub1.field +
> >sub2.field where there is no sub report for the linked
> >field.
> >
> >Hope I explained this okay.
> >TIA
> >Nancy L
> >
> >N_Lytle at terpalum.umd.edu
> >240-274-0069
> >
> >
> >---
> >Outgoing mail is certified Virus Free.
> >Checked by AVG anti-virus system
> >(http://www.grisoft.com).
> >Version: 6.0.524 / Virus Database: 321 - Release Date:
> >10/6/2003
>
> <TEXTAREA NAME="Signature" ROWS="4" COLS="60">
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.524 / Virus Database: 321 - Release Date: 10/6/2003