Access 2003

I posted this last week; unfortunately, none of the suggested
solutions cured the problem

I have a subform which lists financial transactions for a client in
the main form, which I total in a textbox named "txtTotalPremium" in
the subform footer with the Control Source "=Sum([PremiumGBP])";
without quotations of course. This works fine until the subform has
no records to return, in which case textbox result is blank. That
would be fine except I need to use the result in another text box on
the main form as part of another calculation. I have tried what seems
like a hundred different combinations of the Nz and IIf functions all
to no avail. It seems to me that the straight forward
"=Nz(Sum([PremiumGBP]),0)" should do it, but unfortunately not;
whatever I do I get a blank textbox and "#Error" in the mainform
textbox, Where do I go from here?

As an afterthought. Should it matter that the Subform is on a Tab
Control?

On Mon, 2 Jun 2008 06:01:00 -0700 (PDT), colin spalding
<colin.mardell@btopenworld.comwrote:

>Access 2003
>
>I posted this last week; unfortunately, none of the suggested
>solutions cured the problem
>
>I have a subform which lists financial transactions for a client in
>the main form, which I total in a textbox named "txtTotalPremium" in
>the subform footer with the Control Source "=Sum([PremiumGBP])";
>without quotations of course. This works fine until the subform has
>no records to return, in which case textbox result is blank. That
>would be fine except I need to use the result in another text box on
>the main form as part of another calculation. I have tried what seems
>like a hundred different combinations of the Nz and IIf functions all
>to no avail. It seems to me that the straight forward
>"=Nz(Sum([PremiumGBP]),0)" should do it, but unfortunately not;
>whatever I do I get a blank textbox and "#Error" in the mainform
>textbox, Where do I go from here?
>
>As an afterthought. Should it matter that the Subform is on a Tab
>Control?

I haven't tried this to confirm, but I wonder if the problem might be
the result of attempting to sum a null. Have you tried

=Sum(Nz([PremiumGBP], 0))

Hope it helps...

I think a standard solution (it may or may not be suitable for your
situation) for this problem is:

Create a query that calculates the aggregate sum of PremiumGBP grouped
on the field linking your sub form.

Left Join to that query in the main form's record-source, or the sub-
form's record-source, whichever is appropriate.

Use that field's value instead of =Sum([PremiumGBP])




On Jun 2, 9:01*am, colin spalding <colin.mard...@btopenworld.com>
wrote:

Access 2003
>
I posted this last week; unfortunately, none of the suggested
solutions cured the problem
>
I have a subform which lists financial transactions for a client in
the main form, which I total in a textbox named "txtTotalPremium" in
the subform footer with the Control Source "=Sum([PremiumGBP])";
without quotations of course. *This works fine until the subform has
no records to return, in which case textbox result is blank. *That
would be fine except I need to use the result in another text box on
the main form as part of another calculation. *I have tried what seems
like a hundred different combinations of the Nz and IIf functions all
to no avail. *It seems to me that the straight forward
"=Nz(Sum([PremiumGBP]),0)" should do it, but unfortunately not;
whatever I do I get a blank textbox and "#Error" in the mainform
textbox, *Where do I go from here?
>
As an afterthought. *Should it matter that the Subform is on a Tab
Control?

On Jun 2, 9:01*am, colin spalding <colin.mard...@btopenworld.com>
wrote:

Access 2003
>
I posted this last week; unfortunately, none of the suggested
solutions cured the problem
>
I have a subform which lists financial transactions for a client in
the main form, which I total in a textbox named "txtTotalPremium" in
the subform footer with the Control Source "=Sum([PremiumGBP])";
without quotations of course. *This works fine until the subform has
no records to return, in which case textbox result is blank. *That
would be fine except I need to use the result in another text box on
the main form as part of another calculation. *I have tried what seems
like a hundred different combinations of the Nz and IIf functions all
to no avail. *It seems to me that the straight forward
"=Nz(Sum([PremiumGBP]),0)" should do it, but unfortunately not;
whatever I do I get a blank textbox and "#Error" in the mainform
textbox, *Where do I go from here?
>
As an afterthought. *Should it matter that the Subform is on a Tab
Control?

a shot in the dark..... have you tried doing a control.refresh or
control.requery of the subform control before the main form calulation
bobh.

Your problem is quite common, and most developers encounter your exact case
quite often.

The problem is that when the sub-form has no records, then the control does
not return a number, and then nz() fails. (because it is NOT a null value
being returned).

The solution is to build a custom "nz()" function when you need to reference
a sub-form total value.

I use:

Public Function nzobj(objvar As Variant) As Currency

' used in place of nz for sub-reports, since the value returned is *not*
a number
' when there is no resulting value for the sub form
'

If IsNumeric(objvar) Then
nzobj = objvar
Else
nzobj = 0
End If

End Function


Remember, you don't need/want to use the above function for the sum in the
sub-form. You can leave that as you have:

eg:

=Sum([PremiumGBP])

However, in your "main" form (parent form), you need the above function.

=(nzobj(ResOptions.Report.txtSumOfPremiumGBP))

I can't quite remember, but my spider sense tells me this was more of a
problem when using reports, then forms. However, either way, the above
should solve your issue.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com