[AccessD] Expense subreport

Hey John
Could you not just use
=IIF([Reports]![MyMain]![MySub].[Report].[HasData],[Reports]![MyMain]![MySub
].[Report]!MyTotal,0)

Tony Septav
Nanaimo, BC
Canada 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, February 22, 2012 1:57 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Expense subreport

Thanks Jim.

I am trying that and regardless of what I do it pulls the value from the
first line record of the subreport.  I have a text control that sums the
values in the detail section, and then a text control that pulls that value
down into the footer.  All that works fine, IOW in the subreport it displays
the sum in the footer.  However when I reference that footer sum control up
in the parent form it displays the value from the first detail.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 2/15/2012 5:35 AM, Jim Dettman wrote:
>
>   You can use the following:
>
> Function AvoidError(n As Variant, varReplaceWith As Variant) as 
> Variant
>
> 10      On Error GoTo AvoidError_Error
>
> 20      AvoidError = Nz(n, varReplaceWith)
>
> AvoidError_Exit:
> 30      Exit Function
>
> AvoidError_Error:
> 40      AvoidError = varReplaceWith
> 50      Resume AvoidError_Exit
>
> End Function
>
>   set your control source to:
>
>
> =AvoidError(Forms![myFormName]![mySubformControlName].Form![mysubformc
> ontrol
> ],0)
>
>    You can use this anytime you expect to have no records and possibly 
> an error returned.  There is one small rub; in later versions of 
> Access, there is an optimization where in some cases, AvoidError will 
> not be called.  This is especially true if a domain function is used as
part of the expression.
> If that happens, have one control like this:
>
> =Sum(<expression>)
>
> And then create a second control that references the first:
>
> =AvoidError(<reference to first control>)
>
> Jim.
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, February 14, 2012 11:24 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Expense subreport
>
> I am adding an expense subreport to my invoice.  When there is data 
> (one or more records) in the subreport all is copacetic, however when 
> there is no data, the subreport disappears entirely and the controls 
> up on the main report which reference the controls in the subreport 
> display #Error.
>
> Any suggestions as to how to handle this?  Do I have to "fake" data 
> for this case?
>

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2113/4825 - Release Date: 02/22/12