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 "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?

[Phil Stanton]

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" <co***********@btopenworld.comwrote in message
news:92**********************************@k30g2000 hse.googlegroups.com...

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?

[colin spalding]

On May 30, 11:51*am, "Phil Stanton" <[e-mail]:

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" <[e-mail] in message

news:92**********************************@k30g2000 hse.googlegroups.com...

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?- 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]

"colin spalding" <co***********@btopenworld.comwrote in message
news:92**********************************@k30g2000 hse.googlegroups.com...

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?

=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).

[colin spalding]

On May 30, 3:50*pm, "paii, Ron" <[e-mail]:

"colin spalding" <[e-mail] in message

news:92**********************************@k30g2000 hse.googlegroups.com...

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?

=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).

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]

"colin spalding" <co***********@btopenworld.comwrote in message
news:83**********************************@m73g2000 hsh.googlegroups.com...
On May 30, 3:50 pm, "paii, Ron" <[e-mail]:

"colin spalding" <[e-mail] in message

news:92**********************************@k30g2000 hse.googlegroups.com...

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?

=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).

>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]

On May 30, 7:03*pm, "paii, Ron" <[e-mail]:

"colin spalding" <[e-mail] in message

news:83**********************************@m73g2000 hsh.googlegroups.com...
On May 30, 3:50 pm, "paii, Ron" <[e-mail]:

"colin spalding" <[e-mail] in message

news:92**********************************@k30g2000 hse.googlegroups.com...

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?

=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).
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?