Re: Nz Function in Control Source

On May 30, 11:51 am, "Phil Stanton" <p...@xxxxxxxxxxxxxxxxxx> wrote:
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...@xxxxxxxxxxxxxxx> wrote in message

news:9240f596-c0c7-476b-8a46-d4d127d44dfe@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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

Relevant Pages

  • Re: Count Only Yes Records on Report
    ... > group summary field and report footer. ... I suspect you placed the Textbox with the ... >>>>control field is a combo box that captures Yes/No. ...
    (microsoft.public.access.reports)
  • Re: Resolving query parameters
    ... In the report footer, add a textbox that counts the primary key. ... The query itself pulls values from 4 different> textboxes on a form for it's criteria. ... > Dim QueryName As String ...
    (microsoft.public.access.formscoding)
  • Re: Resolving query parameters
    ... In the report footer, add a textbox that counts the primary key. ... The query itself pulls values from 4 different> textboxes on a form for it's criteria. ... > Dim QueryName As String ...
    (microsoft.public.access.queries)
  • Re: Counting Date/Time fields
    ... Place a textbox in the detail section. ... and set its Running Sum property to Over All. ... In the report footer, place a textbox and set its Control Source to ...
    (microsoft.public.access.reports)
  • Re: Counting and displaying line items
    ... Put a textbox in the report footer and put the following expression in the ... PC Datasheet ... Your Resource For Help With Access, ...
    (microsoft.public.access.reports)