469,326 Members | 1,541 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Nz Functions in Control Source

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?

Jun 27 '08 #1
4 2584
On Mon, 2 Jun 2008 06:01:00 -0700 (PDT), colin spalding
<co***********@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...
Jun 27 '08 #2
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?
Jun 27 '08 #3
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.
Jun 27 '08 #4
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
pl*****************@msn.com
Jun 27 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

76 posts views Thread by Nick Coghlan | last post: by
9 posts views Thread by Mikhail Teterin | last post: by
5 posts views Thread by moumou | last post: by
5 posts views Thread by Eric Fortier | last post: by
2 posts views Thread by Bruce One | last post: by
18 posts views Thread by Franky | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.