Nz Function in Control Source

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

colin spalding - 30 May 2008 11:12 GMT

Access 2003

I 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 is blank. That would be fine
except I need to use the figure in another text box on the main form
as part of another calculation. I have tried hundred different
combination 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" and/or #Name?" in the mainform textbox, Where do I go from
here?

Reply to this Message

Phil Stanton - 30 May 2008 11:51 GMT

Several things

Make sure your total is in the Report Footer, not the page footer

The expression you want is

=Sum(IIf(IsNull([PremiumGBP]),0,[PremiumGBP]))

HTH

Phil

> Access 2003
>
[quoted text clipped - 10 lines]
> "#Error" and/or #Name?" in the mainform textbox, Where do I go from
> here?

Reply to this Message

colin spalding - 30 May 2008 15:19 GMT

> Several things
>
[quoted text clipped - 24 lines]
>
> - Show quoted text -

Thanks Phil

But still no joy. Is it because I am talking about a form not a
report?

Reply to this Message

paii, Ron - 30 May 2008 15:50 GMT

> Access 2003
>
[quoted text clipped - 10 lines]
> "#Error" and/or #Name?" in the mainform textbox, Where do I go from
> here?

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

NZ needs to be inside the sum otherwise you may try to sum a NULL.

You get #Name when subform is empty because [PremiumGBP] field does not
exist. You will need to check for a no data condition in the subform.

Is [PremiumGBP] a numeric field? If not you will need
=Sum(iif(IsNumeric(NZ([PremiumGBP],0)),[PremiumGBP],0).

Reply to this Message

colin spalding - 30 May 2008 18:31 GMT

> > Access 2003
>
[quoted text clipped - 20 lines]
> Is [PremiumGBP] a numeric field? If not you will need
> =Sum(iif(IsNumeric(NZ([PremiumGBP],0)),[PremiumGBP],0).

Thanks Ron

Yes the field [PremiumGBP] definitely exists and yes it is numeric
(assuming Currency counts as numeric). I have tried both solutions
you suggest but still no joy.

Reply to this Message

paii, Ron - 30 May 2008 19:03 GMT

On May 30, 3:50 pm, "paii, Ron" <n...@no.com> wrote:

> "colin spalding" <colin.mard...@btopenworld.com> wrote in message
>
[quoted text clipped - 24 lines]
> Is [PremiumGBP] a numeric field? If not you will need
> =Sum(iif(IsNumeric(NZ([PremiumGBP],0)),[PremiumGBP],0).

>Thanks Ron

>Yes the field [PremiumGBP] definitely exists and yes it is numeric
>(assuming Currency counts as numeric). I have tried both solutions
>you suggest but still no joy.

Replace your current calculation with a user function to check the status of
[PremiumGBP], I think you will find [PremiumGBP] does not exist in the
subform when it is empty.

Reply to this Message

colin spalding - 31 May 2008 07:06 GMT

> On May 30, 3:50 pm, "paii, Ron" <n...@no.com> wrote:
>
[quoted text clipped - 36 lines]
>
> - Show quoted text -

I'm sorry but don't understand what a "user function" means. And if
it doesn't exist when the subform is empty, how do i solve the problem?

Reply to this Message