Nz Function in Control Source

[colin spalding]

Access 2003

I subform which lists financial transactions for a client in the main
form, which i total in a textbox named "txtTotalPremiu m" 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?

[Phil Stanton]

Re: Nz Function in Control Source

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


"colin spalding" <colin.mardell@ btopenworld.com wrote in message
news:9240f596-c0c7-476b-8a46-d4d127d44dfe@k3 0g2000hse.googl egroups.com...

Access 2003
>
I subform which lists financial transactions for a client in the main
form, which i total in a textbox named "txtTotalPremiu m" 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?

[colin spalding]

Re: Nz Function in Control Source

On May 30, 11:51 am, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:

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
>
"colin spalding" <colin.mard...@ btopenworld.com wrote in message
>
news:9240f596-c0c7-476b-8a46-d4d127d44dfe@k3 0g2000hse.googl egroups.com...
>
>
>

Access 2003

>

I subform which lists financial transactions for a client in the main
form, which i total in a textbox named "txtTotalPremiu m" 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?- Hide quoted text -

>
- Show quoted text -

Thanks Phil

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

[paii, Ron]

Re: Nz Function in Control Source


"colin spalding" <colin.mardell@ btopenworld.com wrote in message
news:9240f596-c0c7-476b-8a46-d4d127d44dfe@k3 0g2000hse.googl egroups.com...

Access 2003
>
I subform which lists financial transactions for a client in the main
form, which i total in a textbox named "txtTotalPremiu m" 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?

=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(IsNume ric(NZ([PremiumGBP],0)),[PremiumGBP],0).

[colin spalding]

Re: Nz Function in Control Source

On May 30, 3:50 pm, "paii, Ron" <n...@no.comwro te:

"colin spalding" <colin.mard...@ btopenworld.com wrote in message
>
news:9240f596-c0c7-476b-8a46-d4d127d44dfe@k3 0g2000hse.googl egroups.com...
>

Access 2003

>

I subform which lists financial transactions for a client in the main
form, which i total in a textbox named "txtTotalPremiu m" 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?

>
=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(IsNume ric(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.

[paii, Ron]

Re: Nz Function in Control Source


"colin spalding" <colin.mardell@ btopenworld.com wrote in message
news:8378f663-c7a6-43e1-92bd-d26c6d98d3d9@m7 3g2000hsh.googl egroups.com...
On May 30, 3:50 pm, "paii, Ron" <n...@no.comwro te:

"colin spalding" <colin.mard...@ btopenworld.com wrote in message
>
news:9240f596-c0c7-476b-8a46-d4d127d44dfe@k3 0g2000hse.googl egroups.com...
>

Access 2003

>

I subform which lists financial transactions for a client in the main
form, which i total in a textbox named "txtTotalPremiu m" 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?

>
=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(IsNume ric(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.

[colin spalding]

Re: Nz Function in Control Source

On May 30, 7:03 pm, "paii, Ron" <n...@no.comwro te:

"colin spalding" <colin.mard...@ btopenworld.com wrote in message
>
news:8378f663-c7a6-43e1-92bd-d26c6d98d3d9@m7 3g2000hsh.googl egroups.com...
On May 30, 3:50 pm, "paii, Ron" <n...@no.comwro te:
>
>
>
>
>

"colin spalding" <colin.mard...@ btopenworld.com wrote in message

>

news:9240f596-c0c7-476b-8a46-d4d127d44dfe@k3 0g2000hse.googl egroups.com...

>

Access 2003

>

I subform which lists financial transactions for a client in the main
form, which i total in a textbox named "txtTotalPremiu m" 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?

>

=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(IsNume ric(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.- Hide quoted text -
>
- 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?